Introduction¶
This project represents our inaugural analysis of four companies within the commercial sector over a three-year period, from 2021 to 2023, segmented by quarters. The database utilized in this analysis is entirely hypothetical and does not reflect actual figures or pertain to any real-world company.
Through comprehensive data analysis, we have extracted key performance indicators (KPIs) for each of the four companies. Based on these KPIs, we have formulated conclusions and developed strategic recommendations for our organization.
Throughout this file, you will find five sections:
Section 1: Loading Financial Statement Data
Section 2: Loading Company Data
Section 3: Loading Sheets
Section 4: Graphs & Analysis
Section 5: Technical Solutions
Scenario: I am a financial analyst at Tech Company, which operates in a commercial market comprising four companies. Our company has been experiencing a decline in both product sales and profits over time, unlike our competitors who are enjoying high profitability.
->NOTE: If you have any confusing just read Print Function in the at the Code it will be highly enough.
Section 1: Loading Financial Statment Data¶
In [61]:
import pandas as pd
# Load the data from the Excel file
income_statement = pd.read_excel("D:/Project/Project_Four_Companies(Orginal).xlsx", sheet_name=0)
# Display the data
print(income_statement)
Company Quarter Year Revenue COGS Gross_Profit \
0 Tech Q1 2021 648721.067585 365390.553223 283330.514362
1 Tech Q2 2021 565249.754651 260978.467697 304271.286954
2 Tech Q3 2021 758622.045788 454160.430384 304461.615404
3 Tech Q4 2021 669066.642633 350268.148408 318798.494225
4 Tech Q1 2022 881818.228323 429209.758804 452608.469519
5 Tech Q2 2022 518743.610482 278454.776529 240288.833953
6 Tech Q3 2022 656602.042863 344828.586156 311773.456707
7 Tech Q4 2022 922799.522699 489664.650759 433134.871940
8 Tech Q1 2023 639024.113874 348729.523457 290294.590418
9 Tech Q2 2023 595525.716668 336002.664362 259523.052306
10 Tech Q3 2023 869982.790405 390270.984178 479711.806227
11 Tech Q4 2023 731066.734889 436447.182319 294619.552570
12 Speed Q1 2021 657219.896957 333620.195106 323599.701850
13 Speed Q2 2021 980669.416730 576060.126769 404609.289961
14 Speed Q3 2021 551447.140951 314556.044680 236891.096271
15 Speed Q4 2021 787047.389353 347392.711681 439654.677671
16 Speed Q1 2022 641031.653250 308822.462947 332209.190303
17 Speed Q2 2022 563315.281537 310093.241999 253222.039539
18 Speed Q3 2022 994683.138278 502146.543435 492536.594843
19 Speed Q4 2022 658016.027782 344924.807930 313091.219852
20 Speed Q1 2023 574976.837636 330688.399495 244288.438140
21 Speed Q2 2023 677224.505265 282788.975064 394435.530201
22 Speed Q3 2023 900921.328154 386849.765961 514071.562193
23 Speed Q4 2023 520672.590515 230877.364243 289795.226272
24 Maclr Q1 2021 575229.744613 309856.269762 265373.474851
25 Maclr Q2 2021 588115.894438 331782.565483 256333.328955
26 Maclr Q3 2021 830948.139220 495346.279861 335601.859359
27 Maclr Q4 2021 610933.602098 327718.019841 283215.582257
28 Maclr Q1 2022 621120.156744 297236.377810 323883.778933
29 Maclr Q2 2022 844889.341463 459039.573737 385849.767726
30 Maclr Q3 2022 573363.850025 306861.701810 266502.148215
31 Maclr Q4 2022 849581.559369 352117.340746 497464.218622
32 Maclr Q1 2023 810912.543471 403149.840872 407762.702599
33 Maclr Q2 2023 634501.103981 256294.720735 378206.383246
34 Maclr Q3 2023 525939.659180 290613.016584 235326.642596
35 Maclr Q4 2023 723581.076825 348445.100355 375135.976470
36 Dain Q1 2021 582244.133098 316333.224678 265910.908420
37 Dain Q2 2021 600186.073299 334554.472986 265631.600313
38 Dain Q3 2021 950352.434181 556525.313618 393827.120563
39 Dain Q4 2021 970665.796932 471660.397862 499005.399070
40 Dain Q1 2022 512619.943003 214572.638369 298047.304635
41 Dain Q2 2022 562047.491459 268560.319206 293487.172253
42 Dain Q3 2022 690394.369552 402281.868634 288112.500918
43 Dain Q4 2022 892522.772136 534628.289309 357894.482827
44 Dain Q1 2023 786444.952238 346214.469206 440230.483032
45 Dain Q2 2023 916610.121470 455940.093058 460670.028412
46 Dain Q3 2023 744888.121062 362261.675096 382626.445965
47 Dain Q4 2023 883214.841891 404155.746803 479059.095088
Operating_Expenses EBIT Interest_Expense EBT \
0 189537.027856 93793.486506 1850.960091 91942.526415
1 127754.920320 176516.366634 4469.459213 172046.907421
2 172230.285950 132231.329454 11492.987498 120738.341955
3 143989.634301 174808.859924 3852.762138 170956.097786
4 256684.690605 195923.778914 5038.351632 190885.427282
5 121972.105903 118316.728050 8258.503047 110058.225003
6 133764.066144 178009.390563 9579.989569 168429.400995
7 250773.384041 182361.487899 6491.117860 175870.370039
8 178965.578110 111329.012308 4776.462794 106552.549514
9 150405.733237 109117.319069 2377.097701 106740.221369
10 212269.461629 267442.344599 12388.679382 255053.665217
11 212876.604657 81742.947913 9481.011281 72261.936632
12 186260.199537 137339.502314 7072.223069 130267.279245
13 241333.436432 163275.853529 4385.402608 158890.450921
14 163765.160809 73125.935462 12871.622100 60254.313361
15 234290.131294 205364.546377 6518.184527 198846.361850
16 171590.749994 160618.440309 3458.298139 157160.142171
17 156533.630864 96688.408674 9234.772234 87453.636440
18 286637.130819 205899.464024 7178.889225 198720.574799
19 142804.850853 170286.368999 13523.581592 156762.787407
20 132367.779794 111920.658346 2565.705570 109354.952776
21 139115.226930 255320.303271 6512.006065 248808.297206
22 216326.665072 297744.897122 13653.368680 284091.528442
23 117836.743791 171958.482481 7281.633809 164676.848672
24 155276.852228 110096.622622 9862.314161 100234.308461
25 134266.296433 122067.032523 4383.931529 117683.100994
26 231040.314646 104561.544713 5102.887925 99458.656789
27 174450.322596 108765.259661 4116.618121 104648.641539
28 173749.618160 150134.160773 6447.176095 143686.984678
29 226940.111790 158909.655937 13039.513230 145870.142706
30 153783.321439 112718.826776 6569.751437 106149.075339
31 218593.603648 278870.614975 7505.947788 271364.667186
32 194733.935850 213028.766749 7960.373835 205068.392914
33 137244.278415 240962.104831 3815.635892 237146.468939
34 148694.739420 86631.903177 5565.895869 81066.007308
35 157471.077579 217664.898891 16793.808828 200871.090063
36 174002.009698 91908.898722 9788.545935 82120.352786
37 122685.047456 142946.552857 3878.051392 139068.501465
38 272291.248229 121535.872334 4735.078825 116800.793509
39 243955.990105 255049.408965 19282.765179 235766.643786
40 145761.335974 152285.968661 2081.432359 150204.536302
41 136948.845128 156538.327125 8831.127714 147707.199411
42 147043.101111 141069.399807 9875.545553 131193.854254
43 209652.387333 148242.095493 7789.504691 140452.590802
44 219762.388431 220468.094600 6858.906781 213609.187820
45 195880.849360 264789.179052 4908.732637 259880.446415
46 166596.533304 216029.912662 4100.806406 211929.106256
47 225677.598504 253381.496584 12799.077940 240582.418644
Tax_Expense Net_Income
0 27582.757925 64359.768491
1 51614.072226 120432.835195
2 36221.502587 84516.839369
3 51286.829336 119669.268450
4 57265.628185 133619.799097
5 33017.467501 77040.757502
6 50528.820298 117900.580696
7 52761.111012 123109.259028
8 31965.764854 74586.784660
9 32022.066411 74718.154958
10 76516.099565 178537.565652
11 21678.580990 50583.355642
12 39080.183774 91187.095472
13 47667.135276 111223.315645
14 18076.294008 42178.019353
15 59653.908555 139192.453295
16 47148.042651 110012.099519
17 26236.090932 61217.545508
18 59616.172440 139104.402359
19 47028.836222 109733.951185
20 32806.485833 76548.466943
21 74642.489162 174165.808044
22 85227.458532 198864.069909
23 49403.054602 115273.794070
24 30070.292538 70164.015923
25 35304.930298 82378.170696
26 29837.597037 69621.059752
27 31394.592462 73254.049077
28 43106.095403 100580.889275
29 43761.042812 102109.099894
30 31844.722602 74304.352737
31 81409.400156 189955.267030
32 61520.517874 143547.875040
33 71143.940682 166002.528257
34 24319.802192 56746.205116
35 60261.327019 140609.763044
36 24636.105836 57484.246950
37 41720.550440 97347.951026
38 35040.238053 81760.555456
39 70729.993136 165036.650650
40 45061.360891 105143.175412
41 44312.159823 103395.039588
42 39358.156276 91835.697978
43 42135.777241 98316.813562
44 64082.756346 149526.431474
45 77964.133924 181916.312490
46 63578.731877 148350.374379
47 72174.725593 168407.693051
In [62]:
import pandas as pd
# Load the data from the Excel file
balance_sheet_assets = pd.read_excel("D:/Project/Project_Four_Companies(Orginal).xlsx", sheet_name=1)
# Display the data
print(balance_sheet_assets)
Company Quarter Year Cash Accounts_Receivable Inventory \
0 Tech Q1 2021 108279.696035 67301.480271 60332.733781
1 Tech Q2 2021 74652.767419 57080.081988 36564.923231
2 Tech Q3 2021 59313.196864 84926.294392 59167.691606
3 Tech Q4 2021 121789.993433 108083.501404 66410.622551
4 Tech Q1 2022 55973.299397 113649.646460 48630.345990
5 Tech Q2 2022 80702.406964 102817.464981 33237.503491
6 Tech Q3 2022 88387.917836 127710.333988 61840.110524
7 Tech Q4 2022 67933.917113 129024.817002 86907.425269
8 Tech Q1 2023 72952.388632 67318.845809 69029.141373
9 Tech Q2 2023 132510.502436 92360.447932 46536.503733
10 Tech Q3 2023 114973.734610 163541.072629 42600.437868
11 Tech Q4 2023 54694.928870 81078.834324 84750.925919
12 Speed Q1 2021 114200.958565 124978.442384 56890.770934
13 Speed Q2 2021 55393.163298 170536.942104 101007.666361
14 Speed Q3 2021 117534.229189 80479.464745 47351.054548
15 Speed Q4 2021 63231.309142 114318.401144 53935.839404
16 Speed Q1 2022 90856.098361 64648.434991 51327.357278
17 Speed Q2 2022 127823.384778 80929.313367 50100.415963
18 Speed Q3 2022 92628.795257 128246.640960 65873.228690
19 Speed Q4 2022 93627.622513 123767.419893 41367.329930
20 Speed Q1 2023 109553.637225 91860.145597 52642.712428
21 Speed Q2 2023 56287.806963 106295.400781 38849.674840
22 Speed Q3 2023 55335.636796 108446.813290 51039.874238
23 Speed Q4 2023 110498.621555 77885.958645 25331.661291
24 Maclr Q1 2021 114329.622685 61252.409840 44240.200578
25 Maclr Q2 2021 52047.859224 108248.985419 66051.513408
26 Maclr Q3 2021 82547.406816 112198.914000 95320.513804
27 Maclr Q4 2021 88719.577469 97199.953035 49054.201332
28 Maclr Q1 2022 60878.424166 114293.748138 34611.873560
29 Maclr Q2 2022 76410.680119 129879.638859 50995.793117
30 Maclr Q3 2022 121765.790647 71789.151836 60030.982164
31 Maclr Q4 2022 57241.350272 143619.666143 48755.255683
32 Maclr Q1 2023 144067.086030 86458.386243 58828.282221
33 Maclr Q2 2023 66584.988635 66362.609494 49833.435655
34 Maclr Q3 2023 58024.924361 89492.608388 32418.398692
35 Maclr Q4 2023 94108.740815 120419.186887 41572.361866
36 Dain Q1 2021 73285.315978 79141.419454 62188.751579
37 Dain Q2 2021 118273.859230 104071.220494 53932.817013
38 Dain Q3 2021 81358.059785 141022.999036 101902.262050
39 Dain Q4 2021 68008.399448 129743.670693 84692.004497
40 Dain Q1 2022 109980.258299 71891.931701 33210.371856
41 Dain Q2 2022 86082.644179 91392.582367 37634.770072
42 Dain Q3 2022 128536.022781 87550.193196 66957.274746
43 Dain Q4 2022 104446.657568 105627.711046 73960.040679
44 Dain Q1 2023 77456.698923 143408.969724 38353.641467
45 Dain Q2 2023 55170.427293 139964.760849 46134.388450
46 Dain Q3 2023 65166.805748 127152.102372 42306.544151
47 Dain Q4 2023 136866.907861 153258.047556 62151.026956
Current_Assets Property_Plant_Equipment Total_Assets
0 235913.910088 443445.515864 6.793594e+05
1 168297.772638 511039.291010 6.793371e+05
2 203407.182862 722988.496856 9.263957e+05
3 296284.117388 429426.488099 7.257106e+05
4 218253.291847 678703.482365 8.969568e+05
5 216757.375436 316050.540578 5.328079e+05
6 277938.362348 612841.345175 8.907797e+05
7 283866.159384 641801.660560 9.256678e+05
8 209300.375814 552724.351005 7.620247e+05
9 271407.454102 451041.792897 7.224492e+05
10 321115.245106 821941.023532 1.143056e+06
11 220524.689113 428255.990883 6.487807e+05
12 296070.171883 633617.792364 9.296880e+05
13 326937.771762 797465.022287 1.124403e+06
14 245364.748481 350245.821518 5.956106e+05
15 231485.549690 742286.382828 9.737719e+05
16 206831.890630 472692.582276 6.795245e+05
17 258853.114108 539829.064392 7.986822e+05
18 286748.664908 601559.502124 8.883082e+05
19 258762.372336 572648.067953 8.314104e+05
20 254056.495250 539313.047440 7.933695e+05
21 201432.882584 482278.109886 6.837110e+05
22 214822.324324 793786.863780 1.008609e+06
23 213716.241491 299182.516403 5.128988e+05
24 219822.233103 368781.788110 5.886040e+05
25 226348.358051 301008.887267 5.273572e+05
26 290066.834620 438239.899446 7.283067e+05
27 234973.731835 461854.272302 6.968280e+05
28 209784.045864 329171.068696 5.389551e+05
29 257286.112095 795165.525293 1.052452e+06
30 253585.924647 553413.775055 8.069997e+05
31 249616.272097 590738.570516 8.403548e+05
32 289353.754493 419547.772412 7.089015e+05
33 182781.033784 402763.089912 5.855441e+05
34 179935.931441 274814.115212 4.547500e+05
35 256100.289569 700231.396819 9.563317e+05
36 214615.487011 302315.846607 5.169313e+05
37 276277.896736 341741.063076 6.180190e+05
38 324283.320870 584137.893142 9.084212e+05
39 282444.074638 695753.771147 9.781978e+05
40 215082.561856 411458.155378 6.265407e+05
41 215109.996617 547018.771991 7.621288e+05
42 283043.490723 482858.694956 7.659022e+05
43 284034.409294 712915.981888 9.969504e+05
44 259219.310114 615757.357389 8.749767e+05
45 241269.576591 681385.054424 9.226546e+05
46 234625.452270 581577.500304 8.162030e+05
47 352275.982373 523842.826028 8.761188e+05
In [63]:
import pandas as pd
# Load the data from the Excel file
balance_sheet_liabilities_shareholders = pd.read_excel("D:/Project/Project_Four_Companies(Orginal).xlsx", sheet_name=2)
# Display the data
print(balance_sheet_liabilities_shareholders)
Company Quarter Year Accounts_Payable Short_Term_Debt \
0 Tech Q1 2021 64309.800422 82956.441273
1 Tech Q2 2021 34109.296172 64509.680525
2 Tech Q3 2021 52608.751658 114660.138863
3 Tech Q4 2021 47387.206854 107439.319696
4 Tech Q1 2022 47422.297362 130525.295078
5 Tech Q2 2022 51468.743384 70189.093059
6 Tech Q3 2022 68961.783438 93159.725128
7 Tech Q4 2022 92628.227070 98568.265844
8 Tech Q1 2023 56606.277594 90380.428100
9 Tech Q2 2023 43996.898421 82808.495316
10 Tech Q3 2023 62431.051884 128085.232662
11 Tech Q4 2023 64435.161984 125725.503383
12 Speed Q1 2021 64771.920422 74728.733236
13 Speed Q2 2021 78769.910648 192557.790972
14 Speed Q3 2021 46571.558397 84371.092592
15 Speed Q4 2021 62834.391630 92974.182260
16 Speed Q1 2022 38498.011463 119103.516480
17 Speed Q2 2022 43218.872800 98777.732044
18 Speed Q3 2022 94898.327698 192935.934117
19 Speed Q4 2022 49201.480085 85095.608000
20 Speed Q1 2023 58342.879803 77500.826460
21 Speed Q2 2023 44772.335254 100302.472458
22 Speed Q3 2023 62280.781267 121022.819985
23 Speed Q4 2023 28680.217522 97510.131777
24 Maclr Q1 2021 34330.440782 58318.147552
25 Maclr Q2 2021 42399.729607 97853.552243
26 Maclr Q3 2021 65109.639988 107651.438546
27 Maclr Q4 2021 58548.830392 121313.390078
28 Maclr Q1 2022 50650.587487 119399.849551
29 Maclr Q2 2022 85334.919750 88028.441808
30 Maclr Q3 2022 40484.687111 65364.472857
31 Maclr Q4 2022 69875.200159 87217.979253
32 Maclr Q1 2023 59586.469154 109524.717745
33 Maclr Q2 2023 41489.507416 122398.895253
34 Maclr Q3 2023 47916.562870 98455.202868
35 Maclr Q4 2023 63729.296226 134468.836469
36 Dain Q1 2021 57001.904841 113184.376700
37 Dain Q2 2021 39148.160979 65784.443944
38 Dain Q3 2021 85215.653741 113397.108303
39 Dain Q4 2021 50291.843849 106790.511322
40 Dain Q1 2022 28161.194211 102248.954843
41 Dain Q2 2022 27862.664838 96622.817872
42 Dain Q3 2022 43685.585153 82916.839636
43 Dain Q4 2022 91711.394045 173418.599302
44 Dain Q1 2023 64621.399081 85810.192856
45 Dain Q2 2023 76721.488067 160292.561678
46 Dain Q3 2023 44534.116390 144498.708322
47 Dain Q4 2023 44532.018626 149465.827102
Current_Liabilities Long_Term_Debt Total_Liabilities Common_Stock \
0 147266.241696 160837.405639 308103.647335 71442.260673
1 98618.976697 127203.576661 225822.553358 79473.945412
2 167268.890522 162519.726056 329788.616577 171887.461570
3 154826.526550 201877.945152 356704.471702 106769.211805
4 177947.592440 209180.185694 387127.778134 177363.248074
5 121657.836444 202106.729145 323764.565588 79501.335949
6 162121.508566 202315.632138 364437.140704 90511.274235
7 191196.492915 284823.054703 476019.547617 128472.552038
8 146986.705694 251021.535338 398008.241033 125989.793707
9 126805.393737 144322.377579 271127.771315 128804.339040
10 190516.284546 244333.512208 434849.796754 157226.023928
11 190160.665367 258044.269711 448204.935078 75005.398035
12 139500.653658 162815.305069 302315.958726 101204.600286
13 271327.701619 261116.691065 532444.392684 135087.183092
14 130942.650990 112767.601076 243710.252065 73998.368590
15 155808.573890 240255.116354 396063.690244 147404.434999
16 157601.527943 170774.448605 328375.976548 86297.836690
17 141996.604845 196709.249642 338705.854487 147594.093470
18 287834.261815 267546.792364 555381.054179 141487.627599
19 134297.088085 221145.435747 355442.523832 150633.427866
20 135843.706263 213838.672894 349682.379157 156983.310467
21 145074.807712 246812.247460 391887.055172 72032.584097
22 183303.601252 192408.619406 375712.220658 156985.468856
23 126190.349299 143953.196793 270143.546092 99425.099719
24 92648.588334 197550.469051 290199.057385 66111.070828
25 140253.281850 197770.515940 338023.797790 71067.775484
26 172761.078534 298476.759049 471237.837583 82858.096016
27 179862.220470 123229.520445 303091.740915 97538.368103
28 170050.437038 189843.723084 359894.160123 55472.614933
29 173363.361558 200333.243980 373696.605538 196869.516188
30 105849.159968 188357.382715 294206.542684 86380.186846
31 157093.179412 278273.101680 435366.281093 140732.952801
32 169111.186899 318536.362368 487647.549267 114434.439992
33 163888.402669 135318.123573 299206.526242 111399.702471
34 146371.765739 197093.591458 343465.357196 78447.021999
35 198198.132695 232472.316075 430670.448770 98406.623277
36 170186.281541 142841.272759 313027.554300 82809.532906
37 104932.604923 183691.787790 288624.392713 119990.349423
38 198612.762044 303768.045094 502380.807138 152858.497983
39 157082.355170 253633.385771 410715.740941 115136.756757
40 130410.149053 116986.571635 247396.720689 88604.123459
41 124485.482710 172560.823054 297046.305764 128558.153374
42 126602.424789 192777.139659 319379.564449 93566.527848
43 265129.993347 260814.037406 525944.030753 179643.551441
44 150431.591936 168638.687559 319070.279496 149287.534696
45 237014.049746 326489.899508 563503.949253 111183.353949
46 189032.824712 177712.227403 366745.052115 89137.960444
47 193997.845728 286846.550645 480844.396373 104564.670474
Retained_Earnings Shareholders_Equity Total_Liabilities_and_Equity
0 299813.517944 371255.778617 6.793594e+05
1 374040.564878 453514.510290 6.793371e+05
2 424719.601570 596607.063141 9.263957e+05
3 262236.921979 369006.133785 7.257106e+05
4 332465.748004 509828.996078 8.969568e+05
5 129542.014477 209043.350426 5.328079e+05
6 435831.292584 526342.566819 8.907797e+05
7 321175.720288 449648.272327 9.256678e+05
8 238026.692080 364016.485786 7.620247e+05
9 322517.136643 451321.475683 7.224492e+05
10 550980.447956 708206.471884 1.143056e+06
11 125570.346883 200575.744918 6.487807e+05
12 526167.405235 627372.005521 9.296880e+05
13 456871.218274 591958.401366 1.124403e+06
14 277901.949344 351900.317934 5.956106e+05
15 430303.807275 577708.242274 9.737719e+05
16 264850.659669 351148.496358 6.795245e+05
17 312382.230543 459976.324013 7.986822e+05
18 191439.485254 332927.112853 8.883082e+05
19 325334.488591 475967.916457 8.314104e+05
20 286703.853066 443687.163533 7.933695e+05
21 219791.353200 291823.937298 6.837110e+05
22 475911.498590 632896.967447 1.008609e+06
23 143330.112082 242755.211801 5.128988e+05
24 232293.893000 298404.963828 5.886040e+05
25 118265.672044 189333.447528 5.273572e+05
26 174210.800467 257068.896483 7.283067e+05
27 296197.895120 393736.263223 6.968280e+05
28 123588.339504 179060.954437 5.389551e+05
29 481885.515662 678755.031850 1.052452e+06
30 426412.970172 512793.157018 8.069997e+05
31 264255.608720 404988.561521 8.403548e+05
32 106819.537646 221253.977638 7.089015e+05
33 174937.894983 286337.597454 5.855441e+05
34 32837.667458 111284.689457 4.547500e+05
35 427254.614340 525661.237618 9.563317e+05
36 121094.246412 203903.779318 5.169313e+05
37 209404.217677 329394.567100 6.180190e+05
38 253181.908891 406040.406874 9.084212e+05
39 452345.348087 567482.104844 9.781978e+05
40 290539.873086 379143.996545 6.265407e+05
41 336524.309470 465082.462844 7.621288e+05
42 352956.093382 446522.621230 7.659022e+05
43 291362.808987 471006.360429 9.969504e+05
44 406618.853311 555906.388007 8.749767e+05
45 247967.327813 359150.681762 9.226546e+05
46 360319.940016 449457.900460 8.162030e+05
47 290709.741554 395274.412028 8.761188e+05
Section 2: Loading Companies Data¶
In [64]:
import pandas as pd
# Load the data from the Excel file
tech = pd.read_excel("D:/Project/P_Adjusted2.xlsx", sheet_name=0)
# Display the data
print(tech)
Income Statement Unnamed: 1 Unnamed: 2 Unnamed: 3 \ 0 NaN 2021 NaN NaN 1 Quarter Q1 Q2 Q3 2 Revenue 648721 565250 758622 3 COGS 365391 260978 454160 4 Gross_Profit 283331 304271 304462 5 Operating_Expenses 189537 127755 172230 6 EBIT 93793 176516 132231 7 Interest_Expense 1851 4469 11493 8 EBT 91943 172047 120738 9 Tax_Expense 27583 51614 36222 10 Net_Income 64360 120433 84517 11 Balance Sheet NaN NaN NaN 12 Assets NaN NaN NaN 13 NaN 2021 NaN NaN 14 Quarter Q1 Q2 Q3 15 Cash 108280 74653 59313 16 Accounts_Receivable 67301 57080 84926 17 Inventory 60333 36565 59168 18 Current_Assets 235914 168298 203407 19 Property_Plant_Equipment 443446 511039 722988 20 Total_Assets 679359 679337 926396 21 Liability & Shareholder's Equity NaN NaN NaN 22 NaN 2021 NaN NaN 23 Quarter Q1 Q2 Q3 24 Accounts_Payable 64310 34109 52609 25 Short_Term_Debt 82956 64510 114660 26 Current_Liabilities 147266 98619 167269 27 Long_Term_Debt 160837 127204 162520 28 Total_Liabilities 308104 225823 329789 29 Common_Stock 71442 79474 171887 30 Retained_Earnings 299814 374041 424720 31 Shareholders_Equity 371256 453515 596607 32 Total_Liabilities_and_Equity 679359 679337 926396 Unnamed: 4 Unnamed: 5 Unnamed: 6 Unnamed: 7 Unnamed: 8 Unnamed: 9 \ 0 NaN 2022 NaN NaN NaN 2023 1 Q4 Q1 Q2 Q3 Q4 Q1 2 669067 881818 518744 656602 922800 639024 3 350268 429210 278455 344829 489665 348730 4 318798 452608 240289 311773 433135 290295 5 143990 256685 121972 133764 250773 178966 6 174809 195924 118317 178009 182361 111329 7 3853 5038 8259 9580 6491 4776 8 170956 190885 110058 168429 175870 106553 9 51287 57266 33017 50529 52761 31966 10 119669 133620 77041 117901 123109 74587 11 NaN NaN NaN NaN NaN NaN 12 NaN NaN NaN NaN NaN NaN 13 NaN 2022 NaN NaN NaN 2023 14 Q4 Q1 Q2 Q3 Q4 Q1 15 121790 55973 80702 88388 67934 72952 16 108084 113650 102817 127710 129025 67319 17 66411 48630 33238 61840 86907 69029 18 296284 218253 216757 277938 283866 209300 19 429426 678703 316051 612841 641802 552724 20 725711 896957 532808 890780 925668 762025 21 NaN NaN NaN NaN NaN NaN 22 NaN 2022 NaN NaN NaN 2023 23 Q4 Q1 Q2 Q3 Q4 Q1 24 47387 47422 51469 68962 92628 56606 25 107439 130525 70189 93160 98568 90380 26 154827 177948 121658 162122 191196 146987 27 201878 209180 202107 202316 284823 251022 28 356704 387128 323765 364437 476020 398008 29 106769 177363 79501 90511 128473 125990 30 262237 332466 129542 435831 321176 238027 31 369006 509829 209043 526343 449648 364016 32 725711 896957 532808 890780 925668 762025 Unnamed: 10 Unnamed: 11 Unnamed: 12 0 NaN NaN NaN 1 Q2 Q3 Q4 2 595526 869983 731067 3 336003 390271 436447 4 259523 479712 294620 5 150406 212269 212877 6 109117 267442 81743 7 2377 12389 9481 8 106740 255054 72262 9 32022 76516 21679 10 74718 178538 50583 11 NaN NaN NaN 12 NaN NaN NaN 13 NaN NaN NaN 14 Q2 Q3 Q4 15 132511 114974 54695 16 92360 163541 81079 17 46537 42600 84751 18 271407 321115 220525 19 451042 821941 428256 20 722449 1143056 648781 21 NaN NaN NaN 22 NaN NaN NaN 23 Q2 Q3 Q4 24 43997 62431 64435 25 82808 128085 125726 26 126805 190516 190161 27 144322 244334 258044 28 271128 434850 448205 29 128804 157226 75005 30 322517 550980 125570 31 451321 708206 200576 32 722449 1143056 648781
In [65]:
import pandas as pd
# Load the data from the Excel file
speed = pd.read_excel("D:/Project/P_Adjusted2.xlsx", sheet_name=1)
# Display the data
print(speed)
Income Statement Unnamed: 1 Unnamed: 2 Unnamed: 3 \ 0 NaN 2021 NaN NaN 1 Quarter Q1 Q2 Q3 2 Revenue 657220 980669 551447 3 COGS 333620 576060 314556 4 Gross_Profit 283331 404609 236891 5 Operating_Expenses 186260 127755 163765 6 EBIT 93793 276854 73126 7 Interest_Expense 7072 4469 12872 8 EBT 130267 158890 60254 9 Tax_Expense 39080 47667 18076 10 Net_Income 91187 111223 42178 11 Balance Sheet NaN NaN NaN 12 Assets NaN NaN NaN 13 NaN 2021 NaN NaN 14 Quarter Q1 Q2 Q3 15 Cash 114201 55393 117534 16 Accounts_Receivable 124978 170537 80479 17 Inventory 56891 101008 47351 18 Current_Assets 296070 326938 245365 19 Property_Plant_Equipment 633618 797465 350246 20 Total_Assets 929688 1124403 595611 21 Liability & Shareholder's Equity NaN NaN NaN 22 NaN 2021 NaN NaN 23 Quarter Q1 Q2 Q3 24 Accounts_Payable 64772 78770 46572 25 Short_Term_Debt 74729 192558 84371 26 Current_Liabilities 139501 271328 130943 27 Long_Term_Debt 162815 261117 112768 28 Total_Liabilities 302316 532444 243710 29 Common_Stock 101205 135087 73998 30 Retained_Earnings 526167 456871 277902 31 Shareholders_Equity 627372 591958 351900 32 Total_Liabilities_and_Equity 929688 1124403 595611 Unnamed: 4 Unnamed: 5 Unnamed: 6 Unnamed: 7 Unnamed: 8 Unnamed: 9 \ 0 NaN 2022 NaN NaN NaN 2023 1 Q4 Q1 Q2 Q3 Q4 Q1 2 787047 641032 563315 994683 658016 574977 3 347393 308822 310093 502147 344925 330688 4 439655 332209 253222 492537 313091 244288 5 234290 171591 156534 286637 142805 132368 6 205365 160618 96688 205899 170286 111921 7 6518 3458 9235 7179 13524 2566 8 198846 157160 87454 198721 156763 109355 9 59654 47148 26236 59616 47029 32806 10 139192 110012 61218 139104 109734 76548 11 NaN NaN NaN NaN NaN NaN 12 NaN NaN NaN NaN NaN NaN 13 NaN 2022 NaN NaN NaN 2023 14 Q4 Q1 Q2 Q3 Q4 Q1 15 63231 90856 127823 92629 93628 109554 16 114318 64648 80929 128247 123767 91860 17 53936 51327 50100 65873 41367 52643 18 231486 206832 258853 286749 258762 254056 19 742286 472693 539829 601560 572648 539313 20 973772 679524 798682 888308 831410 793370 21 NaN NaN NaN NaN NaN NaN 22 NaN 2022 NaN NaN NaN 2023 23 Q4 Q1 Q2 Q3 Q4 Q1 24 62834 38498 43219 94898 49201 58343 25 92974 119104 98778 192936 85096 77501 26 155809 157602 141997 287834 134297 135844 27 240255 170774 196709 267547 221145 213839 28 396064 328376 338706 555381 355443 349682 29 147404 86298 147594 141488 150633 156983 30 430304 264851 312382 191439 325334 286704 31 577708 351148 459976 332927 475968 443687 32 973772 679524 798682 888308 831410 793370 Unnamed: 10 Unnamed: 11 Unnamed: 12 0 NaN NaN NaN 1 Q2 Q3 Q4 2 677225 900921 520673 3 282789 386850 230877 4 394436 514072 289795 5 139115 216327 117837 6 255320 297745 171958 7 6512 13653 7282 8 248808 284092 164677 9 74642 85227 49403 10 174166 198864 115274 11 NaN NaN NaN 12 NaN NaN NaN 13 NaN NaN NaN 14 Q2 Q3 Q4 15 56288 55336 110499 16 106295 108447 77886 17 38850 51040 25332 18 201433 214822 213716 19 482278 793787 299183 20 683711 1008609 512899 21 NaN NaN NaN 22 NaN NaN NaN 23 Q2 Q3 Q4 24 44772 62281 28680 25 100302 121023 97510 26 145075 183304 126190 27 246812 192409 143953 28 391887 375712 270144 29 72033 156985 99425 30 219791 475911 143330 31 291824 632897 242755 32 683711 1008609 512899
In [66]:
import pandas as pd
# Load the data from the Excel file
maclr = pd.read_excel("D:/Project/P_Adjusted2.xlsx", sheet_name=2)
# Display the data
print(maclr)
Income Statement Unnamed: 1 Unnamed: 2 Unnamed: 3 \ 0 NaN 2021 NaN NaN 1 Quarter Q1 Q2 Q3 2 Revenue 575230 588116 830948 3 COGS 309856 331783 495346 4 Gross_Profit 283331 256333 335602 5 Operating_Expenses 155277 127755 231040 6 EBIT 93793 128578 104562 7 Interest_Expense 9862 4469 5103 8 EBT 100234 117683 99459 9 Tax_Expense 30070 35305 29838 10 Net_Income 70164 82378 69621 11 Balance Sheet NaN NaN NaN 12 Assets NaN NaN NaN 13 NaN 2021 NaN NaN 14 Quarter Q1 Q2 Q3 15 Cash 114330 52048 82547 16 Accounts_Receivable 61252 108249 112199 17 Inventory 44240 66052 95321 18 Current_Assets 219822 226348 290067 19 Property_Plant_Equipment 368782 301009 438240 20 Total_Assets 588604 527357 728307 21 Liability & Shareholder's Equity NaN NaN NaN 22 NaN 2021 NaN NaN 23 Quarter Q1 Q2 Q3 24 Accounts_Payable 34330 42400 65110 25 Short_Term_Debt 58318 97854 107651 26 Current_Liabilities 92649 140253 172761 27 Long_Term_Debt 197550 197771 298477 28 Total_Liabilities 290199 338024 471238 29 Common_Stock 66111 71068 82858 30 Retained_Earnings 232294 118266 174211 31 Shareholders_Equity 298405 189333 257069 32 Total_Liabilities_and_Equity 588604 527357 728307 Unnamed: 4 Unnamed: 5 Unnamed: 6 Unnamed: 7 Unnamed: 8 Unnamed: 9 \ 0 NaN 2022 NaN NaN NaN 2023 1 Q4 Q1 Q2 Q3 Q4 Q1 2 610934 621120 844889 573364 849582 810913 3 327718 297236 459040 306862 352117 403150 4 283216 323884 385850 266502 497464 407763 5 174450 173750 226940 153783 218594 194734 6 108765 150134 158910 112719 278871 213029 7 4117 6447 13040 6570 7506 7960 8 104649 143687 145870 106149 271365 205068 9 31395 43106 43761 31845 81409 61521 10 73254 100581 102109 74304 189955 143548 11 NaN NaN NaN NaN NaN NaN 12 NaN NaN NaN NaN NaN NaN 13 NaN 2022 NaN NaN NaN 2023 14 Q4 Q1 Q2 Q3 Q4 Q1 15 88720 114330 52048 82547 88720 60878 16 97200 61252 108249 112199 97200 114294 17 49054 44240 66052 95321 49054 34612 18 234974 219822 226348 290067 234974 209784 19 461854 368782 301009 438240 461854 329171 20 696828 588604 527357 728307 696828 538955 21 NaN NaN NaN NaN NaN NaN 22 NaN 2022 NaN NaN NaN 2023 23 Q4 Q1 Q2 Q3 Q4 Q1 24 58549 34330 42400 65110 58549 50651 25 121313 58318 97854 107651 121313 119400 26 179862 92649 140253 172761 179862 170050 27 123230 197550 197771 298477 123230 189844 28 303092 290199 338024 471238 303092 359894 29 97538 66111 71068 82858 97538 55473 30 296198 232294 118266 174211 296198 123588 31 393736 298405 189333 257069 393736 179061 32 696828 588604 527357 728307 696828 538955 Unnamed: 10 Unnamed: 11 Unnamed: 12 0 NaN NaN NaN 1 Q2 Q3 Q4 2 634501 525940 723581 3 256295 290613 348445 4 378206 235327 375136 5 137244 148695 157471 6 240962 86632 217665 7 3816 5566 16794 8 237146 81066 200871 9 71144 24320 60261 10 166003 56746 140610 11 NaN NaN NaN 12 NaN NaN NaN 13 NaN NaN NaN 14 Q2 Q3 Q4 15 76411 121766 57241 16 129880 71789 143620 17 50996 60031 48755 18 257286 253586 249616 19 795166 553414 590739 20 1052452 807000 840355 21 NaN NaN NaN 22 NaN NaN NaN 23 Q2 Q3 Q4 24 85335 40485 69875 25 88028 65364 87218 26 173363 105849 157093 27 200333 188357 278273 28 373697 294207 435366 29 196870 86380 140733 30 481886 426413 264256 31 678755 512793 404989 32 1052452 807000 840355
In [67]:
import pandas as pd
# Load the data from the Excel file
dain = pd.read_excel("D:/Project/P_Adjusted2.xlsx", sheet_name=3)
# Display the data
print(dain)
Income Statement Unnamed: 1 Unnamed: 2 Unnamed: 3 \ 0 NaN 2021 NaN NaN 1 Quarter Q1 Q2 Q3 2 Revenue 582244 600186 950352 3 COGS 316333 334554 556525 4 Gross_Profit 283331 265632 393827 5 Operating_Expenses 174002 127755 272291 6 EBIT 93793 137877 121536 7 Interest_Expense 9789 4469 4735 8 EBT 82120 139069 116801 9 Tax_Expense 24636 41721 35040 10 Net_Income 57484 97348 81761 11 Balance Sheet NaN NaN NaN 12 Assets NaN NaN NaN 13 NaN 2021 NaN NaN 14 Quarter Q1 Q2 Q3 15 Cash 73285 118274 81358 16 Accounts_Receivable 79141 104071 141023 17 Inventory 62189 53933 101902 18 Current_Assets 214615 276278 324283 19 Property_Plant_Equipment 302316 341741 584138 20 Total_Assets 516931 618019 908421 21 Liability & Shareholder's Equity NaN NaN NaN 22 NaN 2021 NaN NaN 23 Quarter Q1 Q2 Q3 24 Accounts_Payable 57002 39148 85216 25 Short_Term_Debt 113184 65784 113397 26 Current_Liabilities 170186 104933 198613 27 Long_Term_Debt 142841 183692 303768 28 Total_Liabilities 313028 288624 502381 29 Common_Stock 82810 119990 152858 30 Retained_Earnings 121094 209404 253182 31 Shareholders_Equity 203904 329395 406040 32 Total_Liabilities_and_Equity 516931 618019 908421 Unnamed: 4 Unnamed: 5 Unnamed: 6 Unnamed: 7 Unnamed: 8 Unnamed: 9 \ 0 NaN 2022 NaN NaN NaN 2023 1 Q4 Q1 Q2 Q3 Q4 Q1 2 970666 512620 562047 690394 892523 786445 3 471660 214573 268560 402282 534628 346214 4 499005 298047 293487 288113 357894 440230 5 243956 145761 136949 147043 209652 219762 6 255049 152286 156538 141069 148242 220468 7 19283 2081 8831 9876 7790 6859 8 235767 150205 147707 131194 140453 213609 9 70730 45061 44312 39358 42136 64083 10 165037 105143 103395 91836 98317 149526 11 NaN NaN NaN NaN NaN NaN 12 NaN NaN NaN NaN NaN NaN 13 NaN 2022 NaN NaN NaN 2023 14 Q4 Q1 Q2 Q3 Q4 Q1 15 68008 109980 86083 128536 104447 77457 16 129744 71892 91393 87550 105628 143409 17 84692 33210 37635 66957 73960 38354 18 282444 215083 215110 283043 284034 259219 19 695754 411458 547019 482859 712916 615757 20 978198 626541 762129 765902 996950 874977 21 NaN NaN NaN NaN NaN NaN 22 NaN 2022 NaN NaN NaN 2023 23 Q4 Q1 Q2 Q3 Q4 Q1 24 50292 28161 27863 43686 91711 64621 25 106791 102249 96623 82917 173419 85810 26 157082 130410 124485 126602 265130 150432 27 253633 116987 172561 192777 260814 168639 28 410716 247397 297046 319380 525944 319070 29 115137 88604 128558 93567 179644 149288 30 452345 290540 336524 352956 291363 406619 31 567482 379144 465082 446523 471006 555906 32 978198 626541 762129 765902 996950 874977 Unnamed: 10 Unnamed: 11 Unnamed: 12 0 NaN NaN NaN 1 Q2 Q3 Q4 2 916610 744888 883215 3 455940 362262 404156 4 460670 382626 479059 5 195881 166597 225678 6 264789 216030 253381 7 4909 4101 12799 8 259880 211929 240582 9 77964 63579 72175 10 181916 148350 168408 11 NaN NaN NaN 12 NaN NaN NaN 13 NaN NaN NaN 14 Q2 Q3 Q4 15 55170 65167 136867 16 139965 127152 153258 17 46134 42307 62151 18 241270 234625 352276 19 681385 581578 523843 20 922655 816203 876119 21 NaN NaN NaN 22 NaN NaN NaN 23 Q2 Q3 Q4 24 76721 44534 44532 25 160293 144499 149466 26 237014 189033 193998 27 326490 177712 286847 28 563504 366745 480844 29 111183 89138 104565 30 247967 360320 290710 31 359151 449458 395274 32 922655 816203 876119
Section 3: Loading Sheets¶
After refining and adjusting the values, we are now ready to proceed with the analysis.
In the file provided below, you will find THREE SHEETS:
DATA SHEET: This sheet contains all the measures and adjustments made.¶
In [68]:
import pandas as pd
# Load the data from the Excel file
data = pd.read_excel("D:/Project/Measures.xlsx", sheet_name=0)
# Display the data
print(data)
Company Year Current Ratio Quick Ratio Debt-To-equity \
0 TECH 21-Q1 1.601955 1.192270 0.656674
1 TECH 21-Q2 1.706546 1.335776 0.422728
2 TECH 21-Q3 1.216049 0.862321 0.464594
3 TECH 21-Q4 1.913652 1.484716 0.838244
4 TECH 22-Q1 1.226503 0.953219 0.666313
5 TECH 22-Q2 1.781697 1.508492 1.302581
6 TECH 22-Q3 1.714383 1.332940 0.561375
7 TECH 22-Q4 1.484683 1.030138 0.852647
8 TECH 23-Q1 1.423941 0.954312 0.937875
9 TECH 23-Q2 2.140346 1.773355 0.503257
10 TECH 23-Q3 1.685500 1.461895 0.525862
11 TECH 23-Q4 1.159676 0.713995 1.913341
12 SPEED 21-Q1 2.122357 1.714540 0.378633
13 SPEED 21-Q2 1.204955 0.832684 0.766396
14 SPEED 21-Q3 1.873834 1.512217 0.560212
15 SPEED 21-Q4 1.485705 1.139537 0.576812
16 SPEED 22-Q1 1.312372 0.986694 0.825514
17 SPEED 22-Q2 1.822953 1.470125 0.642396
18 SPEED 22-Q3 0.996228 0.767370 1.383134
19 SPEED 22-Q4 1.926791 1.618762 0.643407
20 SPEED 23-Q1 1.870212 1.482688 0.656633
21 SPEED 23-Q2 1.388476 1.120685 1.189466
22 SPEED 23-Q3 1.171948 0.893504 0.495233
23 SPEED 23-Q4 1.693602 1.492860 0.994678
24 MACLR 21-Q1 2.372645 1.895140 0.857454
25 MACLR 21-Q2 1.613854 1.142910 1.561394
26 MACLR 21-Q3 1.679006 1.127258 1.579842
27 MACLR 21-Q4 1.306410 1.033678 0.621083
28 MACLR 22-Q1 2.372645 1.895140 0.857454
29 MACLR 22-Q2 1.613854 1.142910 1.561394
30 MACLR 22-Q3 1.679006 1.127258 1.579842
31 MACLR 22-Q4 1.306410 1.033678 0.621083
32 MACLR 23-Q1 1.233658 1.030119 1.727030
33 MACLR 23-Q2 1.484086 1.189930 0.424839
34 MACLR 23-Q3 2.395729 1.828592 0.494784
35 MACLR 23-Q4 1.588970 1.278611 0.902473
36 DAIN 21-Q1 1.261062 0.895646 1.255620
37 DAIN 21-Q2 2.632908 2.118932 0.757378
38 DAIN 21-Q3 1.632742 1.119672 1.027398
39 DAIN 21-Q4 1.798064 1.258907 0.635128
40 DAIN 22-Q1 1.649278 1.394617 0.578238
41 DAIN 22-Q2 1.727993 1.425670 0.578787
42 DAIN 22-Q3 2.235688 1.706809 0.617424
43 DAIN 22-Q4 1.071302 0.792345 0.921925
44 DAIN 23-Q1 1.723171 1.468213 0.457719
45 DAIN 23-Q2 1.017955 0.823306 1.355371
46 DAIN 23-Q3 1.241189 1.017384 0.716888
47 DAIN 23-Q4 1.815876 1.495506 1.103821
Debt-To-Assets Debt-To-Captial Interest-Coverage Recivables Turnover \
0 0.358858 0.396381 50.672889 19.278062
1 0.282206 0.297125 39.493899 9.088964
2 0.299202 0.317217 11.505392 10.684338
3 0.426227 0.456003 45.372347 7.018851
4 0.378731 0.399873 38.886484 5.887098
5 0.511058 0.565705 14.326655 8.576541
6 0.331704 0.359539 18.581376 4.061877
7 0.414178 0.460232 28.094004 5.088959
8 0.448020 0.483971 23.307836 13.707893
9 0.314390 0.334778 45.903590 6.918807
10 0.325810 0.344633 21.587640 3.641444
11 0.591525 0.656751 8.621754 10.730085
12 0.255509 0.274644 13.262235 10.517332
13 0.403480 0.433875 61.943595 6.637011
14 0.330986 0.359061 5.681175 4.393714
15 0.342205 0.365809 31.506403 4.823783
16 0.426589 0.452209 46.444359 12.174268
17 0.369968 0.391133 10.470037 7.920883
18 0.518382 0.580384 28.681243 4.392437
19 0.368339 0.391508 12.591810 8.036712
20 0.367218 0.396366 43.621786 7.163237
21 0.507692 0.543268 39.207627 5.409235
22 0.310756 0.331208 21.807431 6.244762
23 0.470782 0.498666 23.615371 11.567185
24 0.434704 0.461629 9.510292 18.782273
25 0.560576 0.609588 28.768225 6.939363
26 0.557633 0.612379 20.490661 7.538726
27 0.350937 0.383128 26.421022 8.548853
28 0.434704 0.461629 23.286809 9.974034
29 0.560576 0.609588 12.186778 5.737884
30 0.557633 0.612379 17.157244 7.530281
31 0.350937 0.383128 37.153285 5.898808
32 0.573784 0.633301 26.761151 7.433316
33 0.273990 0.298166 63.151231 6.243569
34 0.314401 0.331007 15.564773 8.838398
35 0.434925 0.474368 12.961020 3.662031
36 0.495280 0.556663 9.581963 14.714018
37 0.403671 0.430970 30.848627 6.551798
38 0.459220 0.506757 25.667128 7.754997
39 0.368457 0.388427 13.226807 7.324846
40 0.349914 0.366382 73.164025 13.501735
41 0.353200 0.366602 17.725746 5.608988
42 0.359960 0.381733 14.284720 6.419717
43 0.435561 0.479688 19.031004 6.536110
44 0.290806 0.313997 32.143329 6.223619
45 0.527589 0.575438 53.942473 5.618878
46 0.394768 0.417551 52.679861 7.208769
47 0.498006 0.524674 19.796856 4.860352
DSO ... Tax Burden Interest Burden Net Profit Margin \
0 18.933438 ... 0.7 0.980266 0.099210
1 40.158594 ... 0.7 0.974680 0.213061
2 34.162155 ... 0.7 0.913084 0.111408
3 52.002810 ... 0.7 0.977960 0.178860
4 61.999984 ... 0.7 0.974284 0.151528
5 42.557948 ... 0.7 0.930200 0.148514
6 89.859944 ... 0.7 0.946183 0.179562
7 71.723898 ... 0.7 0.964405 0.133408
8 26.626995 ... 0.7 0.957096 0.116720
9 52.754760 ... 0.7 0.978215 0.125466
10 100.234952 ... 0.7 0.953677 0.205220
11 34.016506 ... 0.7 0.884014 0.069191
12 34.704618 ... 0.7 1.388873 0.138747
13 54.994636 ... 0.7 0.573913 0.113416
14 83.073228 ... 0.7 0.823980 0.076486
15 75.666756 ... 0.7 0.968260 0.176854
16 29.981268 ... 0.7 0.978469 0.171617
17 46.080719 ... 0.7 0.904489 0.108674
18 83.097380 ... 0.7 0.965134 0.139848
19 45.416582 ... 0.7 0.920583 0.166765
20 50.954615 ... 0.7 0.977076 0.133133
21 67.477190 ... 0.7 0.974495 0.257176
22 58.448988 ... 0.7 0.954144 0.220734
23 31.554781 ... 0.7 0.957655 0.221394
24 19.433218 ... 0.7 1.068670 0.121976
25 52.598484 ... 0.7 0.915263 0.140071
26 48.416670 ... 0.7 0.951197 0.083785
27 42.695785 ... 0.7 0.962151 0.119905
28 36.595024 ... 0.7 0.957057 0.161935
29 63.612297 ... 0.7 0.917944 0.120855
30 48.470967 ... 0.7 0.941716 0.129594
31 61.876909 ... 0.7 0.973084 0.223587
32 49.103253 ... 0.7 0.962632 0.177020
33 58.460149 ... 0.7 0.984165 0.261627
34 41.297076 ... 0.7 0.935752 0.107895
35 99.671468 ... 0.7 0.922846 0.194325
36 24.806277 ... 0.7 0.875544 0.098729
37 55.709901 ... 0.7 1.008644 0.162196
38 47.066429 ... 0.7 0.961040 0.086032
39 49.830398 ... 0.7 0.924396 0.170024
40 27.033563 ... 0.7 0.986332 0.205109
41 65.074122 ... 0.7 0.943585 0.183961
42 56.856086 ... 0.7 0.929995 0.133019
43 55.843611 ... 0.7 0.947454 0.110156
44 58.647550 ... 0.7 0.968889 0.190130
45 64.959585 ... 0.7 0.981462 0.198466
46 50.632779 ... 0.7 0.981017 0.199158
47 75.097435 ... 0.7 0.949487 0.190676
Asset-Turnover Return-On-Assets Equity-Multiplier Return-On-Equity \
0 0.954901 0.094736 1.829896 0.173357
1 0.832061 0.150004 1.497939 0.265555
2 0.818896 0.102314 1.552774 0.141662
3 0.921947 0.329799 1.966663 0.324302
4 0.983122 0.186912 1.759329 0.262087
5 0.973603 0.108235 2.548792 0.368540
6 0.737109 0.129814 1.692395 0.224000
7 0.996901 0.265990 2.058649 0.273790
8 0.838587 0.100489 2.093380 0.204899
9 0.824315 0.080105 1.600742 0.165554
10 0.761102 0.199279 1.614016 0.252098
11 1.126832 0.155934 3.234592 0.252191
12 0.706925 0.098084 1.481877 0.145348
13 0.872169 0.129328 1.899463 0.187890
14 0.925852 0.053751 1.692555 0.119858
15 0.808246 0.285883 1.685577 0.240939
16 0.943353 0.148845 1.935148 0.313292
17 0.705306 0.072576 1.736355 0.133088
18 1.119750 0.161776 2.668176 0.417822
19 0.791445 0.263971 1.746778 0.230549
20 0.724728 0.103648 1.788128 0.172528
21 0.990513 0.205831 2.342889 0.596818
22 0.893231 0.261404 1.593639 0.314212
23 1.015157 0.449499 2.112823 0.474856
24 0.977278 0.119204 1.972501 0.235130
25 1.115213 0.156209 2.785336 0.435096
26 1.140932 0.095593 2.833119 0.270826
27 0.876735 0.105125 1.769784 0.186049
28 1.055243 0.170880 1.972501 0.337062
29 1.602120 0.193624 2.785336 0.539308
30 0.787256 0.102023 2.833119 0.289045
31 1.219213 0.272600 1.769784 0.482443
32 1.504601 0.266345 3.009897 0.801670
33 0.602879 0.157729 1.550562 0.244569
34 0.651722 0.070318 1.573733 0.110661
35 0.861042 0.167322 2.075009 0.347194
36 1.126347 0.111203 2.535173 0.281918
37 0.971145 0.157516 1.876227 0.295536
38 1.046158 0.090003 2.237268 0.201361
39 0.992300 0.168715 1.723751 0.290823
40 0.818175 0.167815 1.652514 0.277317
41 0.737471 0.135666 1.638696 0.222315
42 0.901413 0.119905 1.715260 0.205669
43 0.895253 0.098618 2.116639 0.208738
44 0.898818 0.170892 1.573964 0.268978
45 0.993449 0.197166 2.568990 0.506518
46 0.912626 0.181757 1.815972 0.330065
47 1.008099 0.192220 2.216482 0.426053
Return-On-Total-Capital Operating-Return-On-Assets Fixed-Asset-Turnover
0 0.252726 0.276123 1.462910
1 0.280123 0.259832 1.106079
2 0.174101 0.164699 1.049286
3 0.225253 0.211619 1.634156
4 0.256469 0.241484 1.338718
5 0.177803 0.165505 1.752213
6 0.273197 0.250086 1.107551
7 0.220395 0.200789 1.533395
8 0.161090 0.131930 1.199540
9 0.157699 0.147011 1.381596
10 0.304071 0.286724 1.084846
11 0.098191 0.091239 1.985213
12 0.166591 0.201774 1.037250
13 0.289817 0.269564 1.229733
14 0.091713 0.085029 1.574457
15 0.281326 0.261714 1.060302
16 0.206987 0.194301 1.356128
17 0.138473 0.130819 1.043507
18 0.265870 0.244103 1.653507
19 0.216152 0.198040 1.149076
20 0.143662 0.137767 1.066128
21 0.371655 0.345709 1.404220
22 0.375640 0.351878 1.134966
23 0.240409 0.226037 1.740318
24 0.249436 0.318698 1.559811
25 0.247449 0.230435 1.953816
26 0.182138 0.166544 1.896103
27 0.167141 0.152639 1.322784
28 0.251786 0.233593 1.684248
29 0.305822 0.284794 2.806858
30 0.196348 0.179537 1.308333
31 0.428545 0.391360 1.839501
32 0.364027 0.344767 2.463499
33 0.331124 0.302829 0.797948
34 0.099943 0.093180 0.950355
35 0.283234 0.264260 1.224875
36 0.291456 0.362886 1.925946
37 0.265454 0.242965 1.756260
38 0.173366 0.159241 1.626932
39 0.291300 0.270377 1.395128
40 0.199551 0.189795 1.245862
41 0.234929 0.225451 1.027474
42 0.193712 0.184642 1.429806
43 0.182177 0.168184 1.251933
44 0.249378 0.235552 1.277199
45 0.319738 0.294598 1.345216
46 0.267099 0.248473 1.280806
47 0.316084 0.299448 1.686030
[48 rows x 27 columns]
CHANGES SHEET: This sheet includes each measure along with the percentage change compared to the previous period.¶
In [69]:
import pandas as pd
# Load the data from the second sheet of the Excel file
changes = pd.read_excel("D:/Project/Measures.xlsx", sheet_name=1)
# Display the data
print(changes)
Company CHANGES % Current Ratio Quick Ratio Debt-To-equity \
0 Tech 21-Q1 0.000000 0.000000 0.000000
1 Tech 21-Q2/21-Q1 0.065289 0.120363 -0.356259
2 Tech 21-Q3/21-Q2 -0.287421 -0.354442 0.099037
3 Tech 21-Q4/21-Q3 0.573664 0.721768 0.804252
4 Tech 22-Q1/21-Q4 -0.359077 -0.357979 -0.205109
5 Tech 22-Q2/22-Q1 0.452664 0.582525 0.954909
6 Tech 22-Q3/22-Q2 -0.037781 -0.116376 -0.569029
7 Tech 22-Q4/22-Q3 -0.133984 -0.227169 0.518856
8 Tech 23-Q1/22-Q4 -0.040912 -0.073607 0.099957
9 Tech 23-Q2/23-Q1 0.503115 0.858254 -0.463407
10 Tech 23-Q3/23-Q2 -0.212510 -0.175633 0.044916
11 Tech 23-Q4/23-Q3 -0.311969 -0.511596 2.638486
12 Speed 21-Q1 0.000000 0.000000 0.000000
13 Speed 21-Q2/21-Q1 -0.432256 -0.514340 1.024110
14 Speed 21-Q3/21-Q2 0.555106 0.816076 -0.269031
15 Speed 21-Q4/21-Q3 -0.207131 -0.246446 0.029633
16 Speed 22-Q1/21-Q4 -0.116667 -0.134127 0.431165
17 Speed 22-Q2/22-Q1 0.389052 0.489949 -0.221823
18 Speed 22-Q3/22-Q2 -0.453508 -0.478024 1.153086
19 Speed 22-Q4/22-Q3 0.934085 1.109493 -0.534819
20 Speed 23-Q1/22-Q4 -0.029364 -0.084061 0.020556
21 Speed 23-Q2/23-Q1 -0.257584 -0.244153 0.811464
22 Speed 23-Q3/23-Q2 -0.155946 -0.202717 -0.583651
23 Speed 23-Q4/23-Q3 0.445117 0.670794 1.008506
24 Maclr 21-Q1 0.000000 0.000000 0.000000
25 Maclr 21-Q2/21-Q1 -0.319808 -0.396926 0.820964
26 Maclr 21-Q3/21-Q2 0.040370 -0.013695 0.011815
27 Maclr 21-Q4/21-Q3 -0.221915 -0.083016 -0.606870
28 Maclr 22-Q1/21-Q4 0.816157 0.833396 0.380579
29 Maclr 22-Q2/22-Q1 -0.319808 -0.396926 0.820964
30 Maclr 22-Q3/22-Q2 0.040370 -0.013695 0.011815
31 Maclr 22-Q4/22-Q3 -0.221915 -0.083016 -0.606870
32 Maclr 23-Q1/22-Q4 -0.055688 -0.003443 1.780674
33 Maclr 23-Q2/23-Q1 0.202996 0.155139 -0.754006
34 Maclr 23-Q3/23-Q2 0.614279 0.536722 0.164639
35 Maclr 23-Q4/23-Q3 -0.336749 -0.300768 0.823973
36 Dain 21-Q1 0.000000 0.000000 0.000000
37 Dain 21-Q2/21-Q1 1.087849 1.365813 -0.396809
38 Dain 21-Q3/21-Q2 -0.379871 -0.471587 0.356519
39 Dain 21-Q4/21-Q3 0.101254 0.124354 -0.381809
40 Dain 22-Q1/21-Q4 -0.082748 0.107800 -0.089573
41 Dain 22-Q2/22-Q1 0.047727 0.022266 0.000949
42 Dain 22-Q3/22-Q2 0.293806 0.197198 0.066756
43 Dain 22-Q4/22-Q3 -0.520818 -0.535774 0.493179
44 Dain 23-Q1/22-Q4 0.608482 0.852998 -0.503518
45 Dain 23-Q2/23-Q1 -0.409255 -0.439246 1.961143
46 Dain 23-Q3/23-Q2 0.219297 0.235729 -0.471076
47 Dain 23-Q4/23-Q3 0.463013 0.469953 0.539741
Debt-To-Assets Debt-To-Captial Interest-Coverage Recivables Turnover \
0 0.000000 0.000000 0.000000 0.000000
1 -0.213600 -0.250405 -0.220611 -0.528533
2 0.060226 0.067621 -0.708679 0.175529
3 0.424543 0.437511 2.943572 -0.343071
4 -0.111433 -0.123092 -0.142947 -0.161245
5 0.349396 0.414713 -0.631578 0.456837
6 -0.350946 -0.364441 0.296979 -0.526397
7 0.248637 0.280062 0.511944 0.252859
8 0.081707 0.051581 -0.170363 1.693653
9 -0.298267 -0.308268 0.969449 -0.495268
10 0.036323 0.029437 -0.529718 -0.473689
11 0.815553 0.905656 -0.600616 1.946656
12 0.000000 0.000000 0.000000 0.000000
13 0.579121 0.579774 3.670675 -0.368945
14 -0.179673 -0.172432 -0.908285 -0.337998
15 0.033895 0.018793 4.545755 0.097883
16 0.246592 0.236188 0.474124 1.523801
17 -0.132730 -0.135061 -0.774568 -0.349375
18 0.401152 0.483853 1.739364 -0.445461
19 -0.289444 -0.325433 -0.560974 0.829670
20 -0.003044 0.012408 2.464298 -0.108686
21 0.382537 0.370622 -0.101192 -0.244862
22 -0.387905 -0.390341 -0.443796 0.154463
23 0.514956 0.505598 0.082905 0.852302
24 0.000000 0.000000 0.000000 0.000000
25 0.289559 0.320515 2.024957 -0.630537
26 -0.005250 0.004580 -0.287733 0.086371
27 -0.370667 -0.374361 0.289418 0.133992
28 0.238695 0.204893 -0.118626 0.166710
29 0.289559 0.320515 -0.476666 -0.424718
30 -0.005250 0.004580 0.407857 0.312379
31 -0.370667 -0.374361 1.165458 -0.216655
32 0.635003 0.652972 -0.279710 0.260139
33 -0.522485 -0.529187 1.359810 -0.160056
34 0.147490 0.110142 -0.753532 0.415600
35 0.383342 0.433106 -0.167285 -0.585668
36 0.000000 0.000000 0.000000 0.000000
37 -0.184964 -0.225796 2.219447 -0.554724
38 0.137610 0.175851 -0.167965 0.183644
39 -0.197646 -0.233504 -0.484679 -0.055468
40 -0.050326 -0.056755 4.531496 0.843279
41 0.009389 0.000601 -0.757726 -0.584573
42 0.019140 0.041273 -0.194126 0.144541
43 0.210027 0.256607 0.332263 0.018131
44 -0.332340 -0.345415 0.688998 -0.047810
45 0.814227 0.832626 0.678186 -0.097169
46 -0.251751 -0.274378 -0.023407 0.282955
47 0.261515 0.256552 -0.624204 -0.325772
DSO ... Tax Burden Interest Burden Net Profit Margin \
0 0.000000 ... 0.000000e+00 0.000000 0.000000
1 1.121041 ... -1.586033e-16 -0.005698 1.147574
2 -0.149319 ... 1.586033e-16 -0.063196 -0.477106
3 0.522234 ... -1.586033e-16 0.071051 0.605445
4 0.192243 ... 1.586033e-16 -0.003759 -0.152815
5 -0.313581 ... 0.000000e+00 -0.045248 -0.019887
6 1.111473 ... 0.000000e+00 0.017182 0.209055
7 -0.201826 ... -1.586033e-16 0.019259 -0.257033
8 -0.628757 ... 0.000000e+00 -0.007579 -0.125094
9 0.981251 ... 1.586033e-16 0.022066 0.074932
10 0.900017 ... -1.586033e-16 -0.025084 0.635661
11 -0.660632 ... 0.000000e+00 -0.073047 -0.662843
12 0.000000 ... 0.000000e+00 0.000000 0.000000
13 0.584649 ... 0.000000e+00 -0.586778 -0.182570
14 0.510570 ... 1.586033e-16 0.435722 -0.325613
15 -0.089156 ... -1.586033e-16 0.175102 1.312238
16 -0.603772 ... 1.586033e-16 0.010543 -0.029610
17 0.536984 ... -1.586033e-16 -0.075607 -0.366767
18 0.803300 ... 0.000000e+00 0.067048 0.286861
19 -0.453454 ... 0.000000e+00 -0.046160 0.192473
20 0.121939 ... 0.000000e+00 0.061366 -0.201672
21 0.324261 ... 1.586033e-16 -0.002641 0.931720
22 -0.133796 ... -1.586033e-16 -0.020883 -0.141700
23 -0.460131 ... 0.000000e+00 0.003679 0.002990
24 0.000000 ... 0.000000e+00 0.000000 0.000000
25 1.706628 ... 0.000000e+00 -0.143549 0.148355
26 -0.079504 ... 0.000000e+00 0.039261 -0.401840
27 -0.118159 ... 0.000000e+00 0.011516 0.431103
28 -0.142889 ... -1.586033e-16 -0.005295 0.350524
29 0.738277 ... 0.000000e+00 -0.040868 -0.253681
30 -0.238025 ... 1.586033e-16 0.025897 0.072307
31 0.276577 ... 0.000000e+00 0.033310 0.725291
32 -0.206437 ... 0.000000e+00 -0.010741 -0.208271
33 0.190556 ... -1.586033e-16 0.022368 0.477949
34 -0.293586 ... 1.586033e-16 -0.049192 -0.587600
35 1.413524 ... 0.000000e+00 -0.013793 0.801057
36 0.000000 ... 0.000000e+00 0.000000 0.000000
37 1.245799 ... 1.586033e-16 0.152019 0.642847
38 -0.155151 ... 0.000000e+00 -0.047196 -0.469582
39 0.058725 ... 0.000000e+00 -0.038129 0.976294
40 -0.457489 ... -1.586033e-16 0.067002 0.206354
41 1.407160 ... 0.000000e+00 -0.043340 -0.103106
42 -0.126287 ... 1.586033e-16 -0.014402 -0.276918
43 -0.017808 ... -1.586033e-16 0.018773 -0.171878
44 0.050211 ... 0.000000e+00 0.022624 0.726001
45 0.107627 ... 0.000000e+00 0.012976 0.043848
46 -0.220550 ... 0.000000e+00 -0.000453 0.003484
47 0.483178 ... 1.586033e-16 -0.032141 -0.042590
Asset-Turnover Return-On-Assets Equity-Multiplier Return-On-Equity \
0 0.000000 0.000000 0.000000 0.000000
1 -0.128642 0.583386 -0.181408 0.531837
2 -0.015822 -0.317923 0.036607 -0.466541
3 0.125841 2.223398 0.266548 1.289255
4 0.066355 -0.433256 -0.105424 -0.191840
5 -0.009682 -0.420931 0.448730 0.406170
6 -0.242906 0.199380 -0.336001 -0.392196
7 0.352447 1.049002 0.216411 0.222279
8 -0.158806 -0.622207 0.016871 -0.251618
9 -0.017019 -0.202850 -0.235331 -0.192022
10 -0.076685 1.487720 0.008292 0.522753
11 0.480526 -0.217510 1.004065 0.000367
12 0.000000 0.000000 0.000000 0.000000
13 0.233750 0.318554 0.281795 0.292696
14 0.061551 -0.584383 -0.108930 -0.362086
15 -0.127024 4.318646 -0.004122 1.010206
16 0.167161 -0.479349 0.148063 0.300297
17 -0.252342 -0.512406 -0.102728 -0.575194
18 0.587609 1.229052 0.536654 2.139433
19 -0.293195 0.631707 -0.345329 -0.448213
20 -0.084299 -0.607349 0.023672 -0.251665
21 0.366738 0.985857 0.310246 2.459253
22 -0.098213 0.269994 -0.319797 -0.473521
23 0.136499 0.719558 0.325785 0.511258
24 0.000000 0.000000 0.000000 0.000000
25 0.141143 0.310437 0.412084 0.850446
26 0.023061 -0.388046 0.017155 -0.377547
27 -0.231562 0.099714 -0.375323 -0.313034
28 0.203605 0.625497 0.114543 0.811687
29 0.518247 0.133098 0.412084 0.600028
30 -0.508616 -0.473085 0.017155 -0.464046
31 0.548686 1.671935 -0.375323 0.669096
32 0.234076 -0.022946 0.700715 0.661690
33 -0.599310 -0.407800 -0.484846 -0.694926
34 0.081016 -0.554189 0.014944 -0.547527
35 0.321180 1.379520 0.318526 2.137459
36 0.000000 0.000000 0.000000 0.000000
37 -0.137792 0.416475 -0.259922 0.048303
38 0.077242 -0.428611 0.192429 -0.318660
39 -0.051482 0.874550 -0.229529 0.444287
40 -0.175476 -0.005332 -0.041327 -0.046439
41 -0.098640 -0.191575 -0.008362 -0.198335
42 0.222304 -0.116174 0.046722 -0.074879
43 -0.006834 -0.177538 0.234005 0.014923
44 0.003982 0.732875 -0.256385 0.288592
45 0.105283 0.153748 0.632179 0.883123
46 -0.081356 -0.078155 -0.293118 -0.348365
47 0.104614 0.057568 0.220549 0.290814
Return-On-Total-Capital Operating-Return-On-Assets Fixed-Asset-Turnover
0 0.000000 0.000000 0.000000
1 0.108404 -0.059000 -0.243919
2 -0.378482 -0.366132 -0.051346
3 0.293804 0.284885 0.557397
4 0.138579 0.141122 -0.180789
5 -0.306726 -0.314632 0.308874
6 0.536515 0.511044 -0.367913
7 -0.193273 -0.197118 0.384491
8 -0.269087 -0.342940 -0.217723
9 -0.021051 0.114310 0.151772
10 0.928178 0.950350 -0.214788
11 -0.677077 -0.681787 0.829949
12 0.000000 0.000000 0.000000
13 0.739689 0.335969 0.185571
14 -0.683549 -0.684566 0.280324
15 2.067464 2.077918 -0.326561
16 -0.264243 -0.257583 0.279002
17 -0.331005 -0.326722 -0.230525
18 0.920007 0.865964 0.584568
19 -0.187002 -0.188703 -0.305068
20 -0.335364 -0.304346 -0.072186
21 1.587005 1.509374 0.317121
22 0.010723 0.017843 -0.191746
23 -0.360001 -0.357627 0.533365
24 0.000000 0.000000 0.000000
25 -0.007966 -0.276948 0.252598
26 -0.263936 -0.277264 -0.029538
27 -0.082339 -0.083493 -0.302367
28 0.506427 0.530369 0.273260
29 0.214608 0.219188 0.666535
30 -0.357966 -0.369592 -0.533880
31 1.182581 1.179836 0.405988
32 -0.150553 -0.119054 0.339221
33 -0.090386 -0.121642 -0.676091
34 -0.698171 -0.692301 0.190998
35 1.833969 1.836013 0.288861
36 0.000000 0.000000 0.000000
37 -0.089214 -0.330464 -0.088106
38 -0.346908 -0.344594 -0.073638
39 0.680265 0.697913 -0.142479
40 -0.314964 -0.298035 -0.106991
41 0.177286 0.187863 -0.175291
42 -0.175441 -0.181009 0.391574
43 -0.059552 -0.089133 -0.124404
44 0.368881 0.400559 0.020182
45 0.282142 0.250670 0.053255
46 -0.164632 -0.156568 -0.047881
47 0.183395 0.205153 0.316382
[48 rows x 27 columns]
RATIOS SHEET: This sheet contains the measures for each quarter of the year for each company Which is comparable:¶
Which Contain Four Main Measures which we will discuss now:
Section 4: Graph & Analysis¶
First: Turnover Measures_1¶
In [70]:
import pandas as pd
import plotly.express as px
import plotly.graph_objects as go
# Define the data
quarters = ['21-Q1', '21-Q2', '21-Q3', '21-Q4',
'22-Q1', '22-Q2', '22-Q3', '22-Q4',
'23-Q1', '23-Q2', '23-Q3', '23-Q4']
# Data for each measure
company_tech_receivable = [19.28, 9.09, 10.68, 7.02, 5.89, 8.58, 4.06, 5.09, 13.71, 6.92, 3.64, 10.73]
company_speed_receivable = [10.52, 6.64, 4.39, 4.82, 12.17, 7.92, 4.39, 8.04, 7.16, 5.41, 6.24, 11.57]
company_maclr_receivable = [18.78, 6.94, 7.54, 8.55, 9.97, 5.74, 7.53, 5.90, 7.43, 6.24, 8.84, 3.66]
company_dain_receivable = [14.71, 6.55, 7.75, 7.32, 13.50, 5.61, 6.42, 6.54, 6.22, 5.62, 7.21, 4.86]
company_tech_payable = [11.36, 5.30, 10.47, 7.01, 9.05, 5.63, 5.73, 7.80, 12.32, 15.27, 7.33, 6.88]
company_speed_payable = [10.30, 8.03, 5.02, 6.35, 6.10, 7.59, 7.27, 4.50, 11.34, 12.63, 7.23, 5.08]
company_maclr_payable = [18.05, 8.65, 9.21, 5.30, 6.40, 11.97, 5.71, 6.08, 15.92, 6.01, 4.62, 6.31]
company_dain_payable = [11.10, 6.96, 8.95, 6.96, 5.47, 9.59, 11.25, 9.87, 10.72, 11.89, 5.98, 9.08]
company_tech_inventory = [12.11, 5.39, 9.49, 5.58, 7.46, 6.80, 7.25, 6.58, 4.47, 5.81, 8.76, 6.85]
company_speed_inventory = [11.73, 7.30, 4.24, 6.86, 5.87, 6.11, 8.66, 6.43, 7.04, 6.18, 8.61, 6.05]
company_maclr_inventory = [14.01, 6.02, 6.14, 4.54, 6.37, 8.32, 3.80, 4.88, 9.64, 5.99, 5.24, 6.41]
company_dain_inventory = [10.17, 5.76, 7.14, 5.06, 3.64, 7.58, 7.69, 7.59, 6.17, 10.79, 8.19, 7.74]
company_tech_cash = [16.95, 39.09, 37.78, 65.33, 70.60, 31.40, 76.44, 80.36, 78.61, 91.63, 92.15, 34.22]
company_speed_cash = [30.39, 59.54, 96.43, 71.40, 32.30, 57.68, 75.05, 21.08, 70.64, 97.63, 50.35, 20.02]
company_maclr_cash = [25.27, 71.06, 68.26, 54.23, 36.85, 76.96, 80.50, 76.71, 64.05, 58.65, 32.01, 98.85]
company_dain_cash = [27.80, 66.60, 57.39, 69.60, 60.59, 75.15, 71.85, 66.98, 83.79, 68.07, 34.10, 82.05]
data_receivable = pd.DataFrame({
'Quarter': quarters * 4,
'Company': ['Tech']*len(quarters) + ['Speed']*len(quarters) + ['Maclr']*len(quarters) + ['Dain']*len(quarters),
'Value': company_tech_receivable + company_speed_receivable + company_maclr_receivable + company_dain_receivable,
'Measure': 'Receivable Turnover'
})
data_payable = pd.DataFrame({
'Quarter': quarters * 4,
'Company': ['Tech']*len(quarters) + ['Speed']*len(quarters) + ['Maclr']*len(quarters) + ['Dain']*len(quarters),
'Value': company_tech_payable + company_speed_payable + company_maclr_payable + company_dain_payable,
'Measure': 'Payable Turnover'
})
data_inventory = pd.DataFrame({
'Quarter': quarters * 4,
'Company': ['Tech']*len(quarters) + ['Speed']*len(quarters) + ['Maclr']*len(quarters) + ['Dain']*len(quarters),
'Value': company_tech_inventory + company_speed_inventory + company_maclr_inventory + company_dain_inventory,
'Measure': 'Inventory Turnover'
})
data_cash = pd.DataFrame({
'Quarter': quarters * 4,
'Company': ['Tech']*len(quarters) + ['Speed']*len(quarters) + ['Maclr']*len(quarters) + ['Dain']*len(quarters),
'Value': company_tech_cash + company_speed_cash + company_maclr_cash + company_dain_cash,
'Measure': 'Cash Conversion Cycle'
})
data = pd.concat([data_receivable, data_payable, data_inventory, data_cash], ignore_index=True)
# Create a numeric quarter column for trend lines
data['Quarter_Num'] = pd.Categorical(data['Quarter'], categories=quarters, ordered=True).codes
# Define custom color palette
my_palette = {
"Tech": "darkslateblue",
"Speed": "lightseagreen",
"Maclr": "darkgoldenrod",
"Dain": "coral"
}
# Plot function
def plot_measure(data, measure, y_label):
data_filtered = data[data['Measure'] == measure]
fig = go.Figure()
for company in data_filtered['Company'].unique():
company_data = data_filtered[data_filtered['Company'] == company]
fig.add_trace(go.Bar(
x=company_data['Quarter'],
y=company_data['Value'],
name=company,
marker_color=my_palette[company]
))
fig.add_trace(go.Scatter(
x=company_data['Quarter'],
y=company_data['Value'],
mode='lines',
name=f'{company} Trend',
line=dict(color=my_palette[company])
))
fig.update_layout(
title=f'{measure} Comparison',
xaxis_title='Year-Quarter',
yaxis_title=y_label,
barmode='group'
)
return fig
# Plot Receivable Turnover
Receivable_Turnover = plot_measure(data, 'Receivable Turnover', 'Receivable Turnover')
Receivable_Turnover.show()
# Plot Payable Turnover
Payable_Turnover = plot_measure(data, 'Payable Turnover', 'Payable Turnover')
Payable_Turnover.show()
# Plot Inventory Turnover
Inventory_Turnover = plot_measure(data, 'Inventory Turnover', 'Inventory Turnover')
Inventory_Turnover.show()
# Plot Cash Conversion Cycle
CCC = plot_measure(data, 'Cash Conversion Cycle', 'Cash Conversion Cycle')
CCC.show()
Conclusions_1:¶
Speed: Speed's dominance has allowed it to impose strict controls on Accounts Receivable, enabling the company to collect its funds promptly at the end of each quarter. For instance, if a customer purchases on account for $100,000 at the beginning of Q1 2021, they have until the end of Q1 2022 to settle the amount due. This practice contributes to fluctuations in the Cash Conversion Cycle each year. These restrictions effectively enhance the company's liquidity and activity ratios, providing it with greater debt accessibility which enhances the Net_Profit_Margin.
Maclr: In contrast to the previous company, Maclr does not impose any restrictions on Accounts Receivables. This is evident from the low Receivable Turnover over the years, which has significantly impacted the Cash Conversion Cycle by extending it. This extension is detrimental to any commercial company aiming to shorten the cycle for quick cash collection and inventory purchases. Consequently, this has also negatively affected the Activity Ratios, showing a decline over the years.
Dain: Dain faces a problem similar to Maclr, as it does not appear to impose any restrictions on Accounts Receivables. The consequences of this have already been discussed in the case of Maclr. One significant difference is that Dain has a high inventory turnover, indicating it sells its products quickly, almost surpassing the dominant company, Speed. This efficiency has also helped Dain reduce its Cash Conversion Cycle. Additionally, Dain has managed to extend its Payable Turnover without adversely affecting its operations over the years. Therefore, its only issue lies in the Receivable Turnover.
Tech: The issue is the lack of restrictions on Accounts Receivables, despite stable inventory turnover rates and an increase in Payable Turnover. The lack of controls on Accounts Receivables has significantly extended the Cash Conversion Cycle due to the decrease in Receivable Turnover Ratio. This situation poses a greater risk to the company, particularly if bad debt and the Allowance for Doubtful Accounts increase, which could severely weaken its liquidity ratios.
Second: Activity Measures_2¶
In [71]:
import pandas as pd
import plotly.express as px
import plotly.graph_objects as go
# Define the data
quarters = ['21-Q1', '21-Q2', '21-Q3', '21-Q4',
'22-Q1', '22-Q2', '22-Q3', '22-Q4',
'23-Q1', '23-Q2', '23-Q3', '23-Q4']
# Current Ratio data
current_ratio_tech = [1.60, 1.71, 1.22, 1.91, 1.23, 1.78, 1.71, 1.48, 1.42, 2.14, 1.69, 1.16]
current_ratio_speed = [2.12, 1.20, 1.87, 1.49, 1.31, 1.82, 1.00, 1.93, 1.87, 1.39, 1.17, 1.69]
current_ratio_maclr = [2.37, 1.61, 1.68, 1.31, 2.37, 1.61, 1.68, 1.31, 1.23, 1.48, 2.40, 1.59]
current_ratio_dain = [1.26, 2.63, 1.63, 1.80, 1.65, 1.73, 2.24, 1.07, 1.72, 1.02, 1.24, 1.82]
# Quick Ratio data
quick_ratio_tech = [1.19, 1.34, 0.86, 1.48, 0.95, 1.51, 1.33, 1.03, 0.95, 1.77, 1.46, 0.71]
quick_ratio_speed = [1.71, 0.83, 1.51, 1.14, 0.99, 1.47, 0.77, 1.62, 1.48, 1.12, 0.89, 1.49]
quick_ratio_maclr = [1.90, 1.14, 1.13, 1.03, 1.90, 1.14, 1.13, 1.03, 1.03, 1.19, 1.83, 1.28]
quick_ratio_dain = [0.90, 2.12, 1.12, 1.26, 1.39, 1.43, 1.71, 0.79, 1.47, 0.82, 1.02, 1.50]
data_current = pd.DataFrame({
'Quarter': quarters * 4,
'Company': ['Tech']*len(quarters) + ['Speed']*len(quarters) + ['Maclr']*len(quarters) + ['Dain']*len(quarters),
'Value': current_ratio_tech + current_ratio_speed + current_ratio_maclr + current_ratio_dain,
'Measure': 'Current Ratio'
})
data_quick = pd.DataFrame({
'Quarter': quarters * 4,
'Company': ['Tech']*len(quarters) + ['Speed']*len(quarters) + ['Maclr']*len(quarters) + ['Dain']*len(quarters),
'Value': quick_ratio_tech + quick_ratio_speed + quick_ratio_maclr + quick_ratio_dain,
'Measure': 'Quick Ratio'
})
data = pd.concat([data_current, data_quick], ignore_index=True)
# Create a numeric quarter column for trend lines
data['Quarter_Num'] = pd.Categorical(data['Quarter'], categories=quarters, ordered=True).codes
# Define custom color palette
my_palette = {
"Tech": "darkslateblue",
"Speed": "lightseagreen",
"Maclr": "darkgoldenrod",
"Dain": "coral"
}
# Plot function
def plot_measure(data, measure, y_label):
data_filtered = data[data['Measure'] == measure]
fig = go.Figure()
for company in data_filtered['Company'].unique():
company_data = data_filtered[data_filtered['Company'] == company]
fig.add_trace(go.Bar(
x=company_data['Quarter'],
y=company_data['Value'],
name=company,
marker_color=my_palette[company]
))
fig.add_trace(go.Scatter(
x=company_data['Quarter'],
y=company_data['Value'],
mode='lines',
name=f'{company} Trend',
line=dict(color=my_palette[company])
))
fig.update_layout(
title=f'{measure} Comparison',
xaxis_title='Year-Quarter',
yaxis_title=y_label,
barmode='group'
)
return fig
# Plot Current Ratio
Current_Ratio = plot_measure(data, 'Current Ratio', 'Current Ratio')
Current_Ratio.show()
# Plot Quick Ratio
Quick_Ratio = plot_measure(data, 'Quick Ratio', 'Quick Ratio')
Quick_Ratio.show()
Conclusions_2:¶
Speed: it is also on a slight downward trend and maintain more stability compared to Dain and Tech.
Maclr: It shows a slight downward trend but is relatively stable and maintain more stability compared to Dain and Tech.
Dain: It is on a downward trend and has the highest fluctuations in the Current Ratio, particularly in Q1 of 2021.
Tech: It exhibits a clear downward trend and a consistent decline, indicating potential liquidity issues.
Third: Solvency Measures_3¶
In [72]:
import pandas as pd
import plotly.express as px
import plotly.graph_objects as go
# Define the data
quarters = ['21-Q1', '21-Q2', '21-Q3', '21-Q4',
'22-Q1', '22-Q2', '22-Q3', '22-Q4',
'23-Q1', '23-Q2', '23-Q3', '23-Q4']
company_tech_debt_to_assets = [36, 28, 30, 43, 38, 51, 33, 41, 45, 31, 33, 59]
company_speed_debt_to_assets = [26, 40, 33, 34, 43, 37, 52, 37, 37, 51, 31, 47]
company_maclr_debt_to_assets = [43, 56, 56, 35, 43, 56, 56, 35, 57, 27, 31, 43]
company_dain_debt_to_assets = [50, 40, 46, 37, 35, 35, 36, 44, 29, 53, 39, 50]
company_tech_debt_to_equity = [66, 42, 46, 84, 67, 130, 56, 85, 94, 50, 53, 191]
company_speed_debt_to_equity = [38, 77, 56, 58, 83, 64, 138, 64, 66, 119, 50, 99]
company_maclr_debt_to_equity = [86, 156, 158, 62, 86, 156, 158, 62, 173, 42, 49, 90]
company_dain_debt_to_equity = [126, 76, 103, 64, 58, 58, 62, 92, 46, 136, 72, 110]
company_tech_debt_to_capital = [40, 30, 32, 46, 40, 57, 36, 46, 48, 33, 34, 66]
company_speed_debt_to_capital = [27, 43, 36, 37, 45, 39, 58, 39, 40, 54, 33, 50]
company_maclr_debt_to_capital = [46, 61, 61, 38, 46, 61, 61, 38, 63, 30, 33, 47]
company_dain_debt_to_capital = [56, 43, 51, 39, 37, 37, 38, 48, 31, 58, 42, 52]
data_debt_to_assets = pd.DataFrame({
'Quarter': quarters,
'Tech': company_tech_debt_to_assets,
'Speed': company_speed_debt_to_assets,
'Maclr': company_maclr_debt_to_assets,
'Dain': company_dain_debt_to_assets,
'Measure': 'Debt_To_Assets'
})
data_debt_to_equity = pd.DataFrame({
'Quarter': quarters,
'Tech': company_tech_debt_to_equity,
'Speed': company_speed_debt_to_equity,
'Maclr': company_maclr_debt_to_equity,
'Dain': company_dain_debt_to_equity,
'Measure': 'Debt_To_Equity'
})
data_debt_to_capital = pd.DataFrame({
'Quarter': quarters,
'Tech': company_tech_debt_to_capital,
'Speed': company_speed_debt_to_capital,
'Maclr': company_maclr_debt_to_capital,
'Dain': company_dain_debt_to_capital,
'Measure': 'Debt_To_Capital'
})
data = pd.concat([data_debt_to_assets, data_debt_to_equity, data_debt_to_capital], ignore_index=True)
# Reshape data from wide to long format
data = pd.melt(data, id_vars=['Quarter', 'Measure'], value_vars=['Tech', 'Speed', 'Maclr', 'Dain'],
var_name='Company', value_name='Value')
# Create a numeric quarter column for trend lines
data['Quarter_Num'] = pd.Categorical(data['Quarter'], categories=quarters, ordered=True).codes
# Define custom color palette
my_palette = {
'Tech': 'darkslateblue',
'Speed': 'lightseagreen',
'Maclr': 'darkgoldenrod',
'Dain': 'coral'
}
# Plot function
def plot_measure(data, measure, y_label):
data_filtered = data[data['Measure'] == measure]
fig = go.Figure()
for company in data_filtered['Company'].unique():
company_data = data_filtered[data_filtered['Company'] == company]
fig.add_trace(go.Bar(
x=company_data['Quarter'],
y=company_data['Value'],
name=company,
marker_color=my_palette[company]
))
fig.add_trace(go.Scatter(
x=company_data['Quarter'],
y=company_data['Value'],
mode='lines',
name=f'{company} Trend',
line=dict(color=my_palette[company])
))
fig.update_layout(
title=f'{measure} Comparison',
xaxis_title='Year-Quarter',
yaxis_title=y_label,
barmode='group'
)
return fig
# Plot Debt-To-Assets
Debt_To_Assets = plot_measure(data, 'Debt_To_Assets', 'Debt-To-Assets (%)')
Debt_To_Assets.show()
# Plot Debt-To-Equity
Debt_To_Equity = plot_measure(data, 'Debt_To_Equity', 'Debt-To-Equity (%)')
Debt_To_Equity.show()
# Plot Debt-To-Capital
Debt_To_Capital = plot_measure(data, 'Debt_To_Capital', 'Debt-To-Capital (%)')
Debt_To_Capital.show()
Conclusions_3:¶
Speed: Regarding the company's debt, the Solvency Ratios indicate that the company leverages its market power and dominance to increase its borrowing capacity. This has significantly impacted the Net Profit Margin, as the higher level of debt has effectively reduced the company’s tax burden.
Maclr: Regarding Solvency, despite the cash flow issue, the company’s Net Profit Margin remains stable and does not decline. This is because the company heavily relies on external debt rather than operating cash flow and liquidity. Although this strategy has reduced taxes and increased net income, it places the company at high risk of bankruptcy if it fails to meet its debt obligations. This issue is further highlighted by the declining Profitability Ratios over the coming years, confirming the problem of reduced operating efficiency.
Dain: Regarding debt, Dain is similar to Maclr, except for the bankruptcy risk. Dain has sufficient liquidity from its operating activities to manage its debt obligations, especially short-term debts.
Tech: Although the company does not rely on debt to the same extent as others, except for the last quarter of the previous year, it still fails to show upward progress in Net Profit Margin trends. This lack of improvement will impact the stability of its operating systems
Finally: Financial_Health Measures_4,5¶
In [73]:
import pandas as pd
import plotly.express as px
import plotly.graph_objects as go
# Define the data
quarters = ['21-Q1', '21-Q2', '21-Q3', '21-Q4',
'22-Q1', '22-Q2', '22-Q3', '22-Q4',
'23-Q1', '23-Q2', '23-Q3', '23-Q4']
company_tech_gross = [44, 54, 40, 48, 51, 46, 47, 47, 45, 44, 55, 40]
company_speed_gross = [43, 41, 43, 56, 52, 45, 50, 48, 42, 58, 57, 56]
company_maclr_gross = [49, 44, 40, 46, 52, 46, 46, 59, 50, 60, 45, 52]
company_dain_gross = [49, 44, 41, 51, 58, 52, 42, 40, 56, 50, 51, 54]
company_tech_ebit = [14, 31, 17, 26, 22, 23, 27, 20, 17, 18, 31, 11]
company_speed_ebit = [14, 28, 13, 26, 25, 17, 21, 26, 19, 38, 33, 33]
company_maclr_ebit = [16, 22, 13, 18, 24, 19, 20, 33, 26, 38, 16, 30]
company_dain_ebit = [16, 23, 13, 26, 30, 28, 20, 17, 28, 29, 29, 29]
company_tech_net = [10, 21, 11, 18, 15, 15, 18, 13, 12, 13, 21, 7]
company_speed_net = [14, 11, 8, 18, 17, 11, 14, 17, 13, 26, 22, 22]
company_maclr_net = [12, 14, 8, 12, 16, 12, 13, 22, 18, 26, 11, 19]
company_dain_net = [10, 16, 9, 17, 21, 18, 13, 11, 19, 20, 20, 19]
data_gross = pd.DataFrame({
'Quarter': quarters,
'Tech': company_tech_gross,
'Speed': company_speed_gross,
'Maclr': company_maclr_gross,
'Dain': company_dain_gross,
'Measure': 'Gross_Profit_Margin'
})
data_ebit = pd.DataFrame({
'Quarter': quarters,
'Tech': company_tech_ebit,
'Speed': company_speed_ebit,
'Maclr': company_maclr_ebit,
'Dain': company_dain_ebit,
'Measure': 'EBIT_Margin'
})
data_net = pd.DataFrame({
'Quarter': quarters,
'Tech': company_tech_net,
'Speed': company_speed_net,
'Maclr': company_maclr_net,
'Dain': company_dain_net,
'Measure': 'Net_Profit_Margin'
})
data = pd.concat([data_gross, data_ebit, data_net], ignore_index=True)
# Use melt to reshape the data
data = pd.melt(data, id_vars=['Quarter', 'Measure'], value_vars=['Tech', 'Speed', 'Maclr', 'Dain'],
var_name='Company', value_name='Value')
# Create a numeric quarter column for trend lines
data['Quarter_Num'] = pd.Categorical(data['Quarter'], categories=quarters, ordered=True).codes
# Define custom color palette
my_palette = {
"Tech": "darkslateblue",
"Speed": "lightseagreen",
"Maclr": "darkgoldenrod",
"Dain": "coral"
}
# Plot function
def plot_measure(data, measure, y_label):
data_filtered = data[data['Measure'] == measure]
fig = go.Figure()
for company in data_filtered['Company'].unique():
company_data = data_filtered[data_filtered['Company'] == company]
fig.add_trace(go.Bar(
x=company_data['Quarter'],
y=company_data['Value'],
name=company,
marker_color=my_palette[company]
))
fig.add_trace(go.Scatter(
x=company_data['Quarter'],
y=company_data['Value'],
mode='lines',
name=f'{company} Trend',
line=dict(color=my_palette[company])
))
fig.update_layout(
title=f'{measure} Comparison',
xaxis_title='Year-Quarter',
yaxis_title=y_label,
barmode='group'
)
return fig
# Plot Gross Profit Margin
Gross_Margin = plot_measure(data, 'Gross_Profit_Margin', 'Gross Profit Margin (%)')
Gross_Margin.show()
# Plot EBIT Margin
EBIT_Margin = plot_measure(data, 'EBIT_Margin', 'EBIT Margin (%)')
EBIT_Margin.show()
# Plot Net Profit Margin
Net_Profit_Margin = plot_measure(data, 'Net_Profit_Margin', 'Net Profit Margin (%)')
Net_Profit_Margin.show()
Conclusions_4:¶
The graphs indicate that all companies exhibit an upward trend in their Margin Ratios, with increases observed each quarter. However, Tech stands out with a different, downward trend. Additionally, it is important to note that Speed Company is the dominant player in this context.
In [74]:
import pandas as pd
import plotly.express as px
import plotly.graph_objects as go
# Define the data
quarters = ['21-Q1', '21-Q2', '21-Q3', '21-Q4',
'22-Q1', '22-Q2', '22-Q3', '22-Q4',
'23-Q1', '23-Q2', '23-Q3', '23-Q4']
company_tech_roa = [9, 15, 10, 33, 19, 11, 13, 27, 10, 8, 20, 16]
company_speed_roa = [10, 13, 5, 29, 15, 7, 16, 26, 10, 21, 26, 45]
company_maclr_roa = [12, 16, 10, 11, 17, 19, 10, 27, 27, 16, 7, 17]
company_dain_roa = [11, 16, 9, 17, 17, 14, 12, 10, 17, 20, 18, 19]
company_tech_roe = [17, 27, 14, 32, 26, 37, 22, 27, 20, 17, 25, 25]
company_speed_roe = [15, 19, 12, 24, 31, 13, 42, 23, 17, 60, 31, 47]
company_maclr_roe = [24, 44, 27, 19, 34, 54, 29, 48, 80, 24, 11, 35]
company_dain_roe = [28, 30, 20, 29, 28, 22, 21, 21, 27, 51, 33, 43]
company_tech_oroa = [28, 26, 16, 21, 24, 17, 25, 20, 13, 15, 29, 9]
company_speed_oroa = [20, 27, 9, 26, 19, 13, 24, 20, 14, 35, 35, 23]
company_maclr_oroa = [32, 23, 17, 15, 23, 28, 18, 39, 34, 30, 9, 26]
company_dain_oroa = [36, 24, 16, 27, 19, 23, 18, 17, 24, 29, 25, 30]
data_roa = pd.DataFrame({
'Quarter': quarters,
'Tech': company_tech_roa,
'Speed': company_speed_roa,
'Maclr': company_maclr_roa,
'Dain': company_dain_roa,
'Measure': 'ROA'
})
data_roe = pd.DataFrame({
'Quarter': quarters,
'Tech': company_tech_roe,
'Speed': company_speed_roe,
'Maclr': company_maclr_roe,
'Dain': company_dain_roe,
'Measure': 'ROE'
})
data_oroa = pd.DataFrame({
'Quarter': quarters,
'Tech': company_tech_oroa,
'Speed': company_speed_oroa,
'Maclr': company_maclr_oroa,
'Dain': company_dain_oroa,
'Measure': 'Operating_ROA'
})
data = pd.concat([data_roa, data_roe, data_oroa], ignore_index=True)
# Use melt to reshape the data
data = pd.melt(data, id_vars=['Quarter', 'Measure'], value_vars=['Tech', 'Speed', 'Maclr', 'Dain'],
var_name='Company', value_name='Value')
# Create a numeric quarter column for trend lines
data['Quarter_Num'] = pd.Categorical(data['Quarter'], categories=quarters, ordered=True).codes
# Define custom color palette
palette_colors = {
"Tech": "darkslateblue",
"Speed": "lightseagreen",
"Maclr": "darkgoldenrod",
"Dain": "coral"
}
# Plot function
def plot_measure(data, measure, y_label):
data_filtered = data[data['Measure'] == measure]
fig = go.Figure()
for company in data_filtered['Company'].unique():
company_data = data_filtered[data_filtered['Company'] == company]
fig.add_trace(go.Bar(
x=company_data['Quarter'],
y=company_data['Value'],
name=company,
marker_color=palette_colors[company]
))
fig.add_trace(go.Scatter(
x=company_data['Quarter'],
y=company_data['Value'],
mode='lines',
name=f'{company} Trend',
line=dict(color=palette_colors[company])
))
fig.update_layout(
title=f'{measure} Comparison',
xaxis_title='Year-Quarter',
yaxis_title=y_label,
barmode='group'
)
return fig
# Plot ROA
ROA = plot_measure(data, 'ROA', 'ROA (%)')
ROA.show()
# Plot ROE
ROE = plot_measure(data, 'ROE', 'ROE (%)')
ROE.show()
# Plot Operating ROA
OROA = plot_measure(data, 'Operating_ROA', 'Operating ROA (%)')
OROA.show()
Conclusions_5:¶
Speed: In terms of profitability, the key indicators, ROE (Return on Equity) and OROA (Operating Return on Operating Assets), show positive performance for two reasons. Firstly, OROA remains strong because the company has maintained stable inventory turnover (meaning the period in which the inventory is quickly sold). Secondly, ROE has remained stable due to the improvement and consistency in the Dupont Analysis.
Maclr & Dain: They are relatively stable in their Operating Return on Assets (OROA) measure. However, Dain's Return on Equity (ROE) shows an upward trend due to its high efficiency in increasing its ROE. This efficiency suggests that Dain's ROE could match that of Maclr in the coming years.
Tech: It appears to be the worst-case scenario compared to other companies in its sector, particularly due to its downward trend in returns from operating activities.
Additional Support Analysis Graphs_6¶
In [75]:
import pandas as pd
import plotly.express as px
import plotly.graph_objects as go
import plotly.io as pio
# Set the default renderer to 'notebook' or 'notebook_connected'
pio.renderers.default = 'notebook'
# Define the data
quarters = ['21-Q1', '21-Q2', '21-Q3', '21-Q4',
'22-Q1', '22-Q2', '22-Q3', '22-Q4',
'23-Q1', '23-Q2', '23-Q3', '23-Q4']
tech_values_cogs = [0.56, 0.46, 0.60, 0.52, 0.49, 0.54, 0.53, 0.53, 0.55, 0.56, 0.45, 0.60]
speed_values_cogs = [0.51, 0.59, 0.57, 0.44, 0.48, 0.55, 0.50, 0.52, 0.58, 0.42, 0.43, 0.44]
maclr_values_cogs = [0.54, 0.56, 0.60, 0.54, 0.48, 0.54, 0.54, 0.41, 0.50, 0.40, 0.55, 0.48]
dain_values_cogs = [0.54, 0.56, 0.59, 0.49, 0.42, 0.48, 0.58, 0.60, 0.44, 0.50, 0.49, 0.46]
data_cogs = pd.DataFrame({
'Quarter': quarters * 4,
'Company': ['Tech']*len(quarters) + ['Speed']*len(quarters) + ['Maclr']*len(quarters) + ['Dain']*len(quarters),
'COGS_to_Revenue_Ratio': tech_values_cogs + speed_values_cogs + maclr_values_cogs + dain_values_cogs
})
# Define custom color palette
my_palette = {
"Tech": "darkslateblue",
"Speed": "lightseagreen",
"Maclr": "darkgoldenrod",
"Dain": "coral"
}
# Plot COGS to Revenue Ratio with linear trends
fig_cogs = go.Figure()
for company in data_cogs['Company'].unique():
company_data = data_cogs[data_cogs['Company'] == company]
fig_cogs.add_trace(go.Bar(
x=company_data['Quarter'],
y=company_data['COGS_to_Revenue_Ratio'],
name=company,
marker_color=my_palette[company]
))
fig_cogs.add_trace(go.Scatter(
x=company_data['Quarter'],
y=company_data['COGS_to_Revenue_Ratio'],
mode='lines',
name=f'{company} Trend',
line=dict(dash='dash', color=my_palette[company])
))
fig_cogs.update_layout(
title='COGS to Revenue Ratio Comparison',
xaxis_title='Year-Quarter',
yaxis_title='COGS to Revenue Ratio',
barmode='group'
)
fig_cogs.show()
# Define the data for Operating Expenses
tech_values_exp = [189537, 127755, 172230, 143990, 256685, 121972, 133764, 250773, 178966, 150406, 212269, 212877]
speed_values_exp = [186260, 127755, 163765, 234290, 171591, 156534, 286637, 142805, 132368, 139115, 216327, 117837]
maclr_values_exp = [155277, 127755, 231040, 174450, 173750, 226940, 153783, 218594, 194734, 137244, 148695, 157471]
dain_values_exp = [174002, 127755, 272291, 243956, 145761, 136949, 147043, 209652, 219762, 195881, 166597, 225678]
data_op_expenses = pd.DataFrame({
'Quarter': quarters * 4,
'Company': ['Tech']*len(quarters) + ['Speed']*len(quarters) + ['Maclr']*len(quarters) + ['Dain']*len(quarters),
'Operating_Expenses': tech_values_exp + speed_values_exp + maclr_values_exp + dain_values_exp
})
# Plot Operating Expenses with linear trends
fig_expenses = go.Figure()
for company in data_op_expenses['Company'].unique():
company_data = data_op_expenses[data_op_expenses['Company'] == company]
fig_expenses.add_trace(go.Bar(
x=company_data['Quarter'],
y=company_data['Operating_Expenses'],
name=company,
marker_color=my_palette[company]
))
fig_expenses.add_trace(go.Scatter(
x=company_data['Quarter'],
y=company_data['Operating_Expenses'],
mode='lines',
name=f'{company} Trend',
line=dict(dash='dash', color=my_palette[company])
))
fig_expenses.update_layout(
title='Operating Expenses Comparison',
xaxis_title='Year-Quarter',
yaxis_title='Operating Expenses',
yaxis=dict(tickformat=','),
barmode='group'
)
fig_expenses.show()
Conclusions_6:¶
All companies are interconnected in terms of costs, as they operate within the same commercial sector and benefit from stable economic conditions.
Tech: Now, addressing the crucial point, the company has declining Margins despite the surrounding companies not experiencing similar issues. If we look at the Gross Profit Margin, it is lower compared to other companies. This could be due to one of two reasons: either the company is not well-known and thus sells less, which is unlikely since the data shows that all four companies have nearly equal profits, or the second and more likely reason is that Tech's product pricing is higher than that of other companies. Upon investigating, it was found that Tech has higher Costs of Goods Sold compared to the other companies.
Regarding the EBIT Margin (Earnings Before Interest and Tax), it has been low over the years, also due to higher Operating Expenses than the other companies. Ultimately, this has resulted in a lower Net Profit Margin. Additionally, the lower debt levels compared to other companies have led to higher taxes for Tech compared to its peers.
Section 5: The Solutions For Tech:¶
1-Cost Analysis and Value Engineering: The first step Tech should take is to thoroughly examine its costs by implementing value engineering techniques. This involves identifying and eliminating all non-value-added activities to reduce overall costs. By doing so, the company can lower its product prices, thereby increasing its Gross Profit Margin and enhancing its inventory turnover.
2-Implementing Appropriate Controls on Accounts Receivables: After reducing prices, the company should introduce suitable controls on Accounts Receivables that align with its market position. This strategy will help increase the Receivable Turnover, thereby shortening the Cash Conversion Cycle. Consequently, this will improve liquidity and boost operating activities.
3-Optimizing Debt-to-Equity Ratio: Once the company stabilizes and its market value increases, it can leverage the third crucial factor: optimizing its Debt-to-Equity ratio. By aligning its debt levels with its financial position and liquidity at that time, the company can increase its Net Profit Margin and benefit from a reduction in taxes.
Implementing these strategies will enhance Tech’s competitiveness with Speed and other Companies and improve its overall financial performance.